📋

Description

This error indicates that a stored procedure or function was invoked with an argument count that does not match its defined signature. It commonly occurs when calling user-defined routines or sometimes built-in functions, preventing successful execution of the SQL statement.

💬

Error Message

Incorrect number of arguments for %s %s; expected %u, got %u

🔍

Known Causes

3 known causes

⚠️

Mismatched Argument Count in Call

The number of arguments supplied when calling a stored procedure or function differs from the number of parameters it expects.

⚠️

Outdated Routine Signature

A stored procedure or function's definition was modified to change its parameter count, but existing calls to it were not updated accordingly.

⚠️

Incorrect Built-in Function Usage

Although less common, some built-in MariaDB functions might throw this error if called with an incorrect number of arguments.

🛠️

Solutions

3 solutions available

1. Verify and Correct Stored Routine Call Arguments easy

Double-check the number of arguments passed to the stored routine and ensure they match the definition.

1

Identify the stored routine being called. The error message will typically include the name of the routine (e.g., 'my_procedure' or 'my_function').

2

Retrieve the stored routine's definition to see how many arguments it expects. Replace `your_routine_name` with the actual name.

SHOW CREATE PROCEDURE your_routine_name;
-- OR for functions
SHOW CREATE FUNCTION your_routine_name;

3

Compare the number of arguments in the `SHOW CREATE` output with the number of arguments in your calling statement. Adjust the calling statement to match the expected number of arguments. If the routine expects 3 arguments, your call must provide exactly 3 arguments.

-- Example: If the routine expects 3 arguments, your call should look like this:
CALL your_routine_name(arg1, arg2, arg3);

2. Review Stored Routine Definition for Argument Count Discrepancy medium

Examine the stored routine's `CREATE` statement to ensure the argument count is as intended.

1

Use `SHOW CREATE PROCEDURE` or `SHOW CREATE FUNCTION` to get the exact definition of the problematic stored routine. Replace `your_routine_name`.

SHOW CREATE PROCEDURE your_routine_name;
-- OR for functions
SHOW CREATE FUNCTION your_routine_name;

2

Carefully count the number of parameters defined within the parentheses of the `CREATE PROCEDURE` or `CREATE FUNCTION` statement. For example, `(param1 INT, param2 VARCHAR(50))` defines two parameters.

3

If the counted number of parameters does not match your intent or the way you are calling the routine, modify the `CREATE` statement accordingly. You can do this by altering the routine.

-- Example: If you intended for a procedure to have 2 arguments but it's defined with 1, you'd modify it.
-- First, drop the existing routine (ensure you have backups or can recreate it)
DROP PROCEDURE IF EXISTS your_routine_name;
-- Then, recreate it with the correct number of arguments
DELIMITER $$
CREATE PROCEDURE your_routine_name(IN arg1 INT, IN arg2 VARCHAR(100))
BEGIN
    -- routine body
END $$
DELIMITER ;

3. Address Missing or Extra Default Values in Routine Parameters medium

Ensure that default values are correctly specified for optional parameters if they are being omitted in the call.

1

Retrieve the stored routine's definition using `SHOW CREATE PROCEDURE` or `SHOW CREATE FUNCTION`.

SHOW CREATE PROCEDURE your_routine_name;
-- OR for functions
SHOW CREATE FUNCTION your_routine_name;

2

Examine the parameter list for any parameters that have `DEFAULT` values specified. For example, `(param1 INT, param2 VARCHAR(50) DEFAULT 'some_value')`.

3

When calling a routine with optional parameters (those with default values), you can omit them. However, if you are omitting a parameter, ensure that all preceding parameters are also omitted or explicitly provided. The error can occur if you skip an argument in the middle without providing a default for it.

-- If your_routine_name is defined as (IN arg1 INT, IN arg2 VARCHAR(100) DEFAULT 'default', IN arg3 DATE DEFAULT NULL)
-- Calling with only arg1 and arg3 is incorrect if arg2 is expected to be skipped:
-- CALL your_routine_name(10, NULL, '2023-10-27'); -- This might be fine if NULL is a valid value for arg2
-- The issue arises if the call is like: CALL your_routine_name(10, '2023-10-27'); -- Missing arg2 and arg3 is ambiguous
-- Correct calls:
CALL your_routine_name(10, 'some_value', '2023-10-27'); -- All provided
CALL your_routine_name(10, DEFAULT, '2023-10-27'); -- Use DEFAULT keyword for omitted optional args
CALL your_routine_name(10);
CALL your_routine_name(10, 'some_value');

4

If you intend for a parameter to be optional and have a default value, ensure it's correctly defined in the `CREATE` statement. If you're encountering this error, consider explicitly passing the `DEFAULT` keyword for omitted parameters in your call to avoid ambiguity.

CALL your_routine_name(arg1, DEFAULT, arg3);

🔗

Related Errors

5 related errors

MariaDB Error 1318: Incorrect Stored Routine Arguments