📋

Description

MariaDB Error 1107 indicates that the number of arguments provided when calling a stored procedure does not match the number of parameters defined in the procedure's signature. This error occurs when a procedure is invoked with either too few or too many arguments than it expects.

💬

Error Message

Incorrect parameter count to procedure '%s'

🔍

Known Causes

4 known causes

⚠️

Missing Required Arguments

The stored procedure call omits one or more parameters that are defined as required in the procedure's signature.

⚠️

Supplying Excess Arguments

The procedure call includes more arguments than the stored procedure is designed to accept according to its definition.

⚠️

Outdated Procedure Definition

The stored procedure's definition was altered (parameters added or removed), but the calling code was not updated to reflect these changes.

⚠️

Typographical Error in Call

A typo in the stored procedure call or an incorrect understanding of its signature leads to an accidental mismatch in argument count.

🛠️

Solutions

3 solutions available

1. Verify Stored Procedure Call Parameters easy

Ensure the number of arguments passed to the stored procedure matches its definition.

1

Identify the stored procedure that is causing the error. The error message '%s' will contain the name of the procedure.

2

Retrieve the definition of the stored procedure to see its expected parameters. Use the `SHOW CREATE PROCEDURE` statement.

SHOW CREATE PROCEDURE your_procedure_name;

3

Compare the number of arguments in your `CALL` statement with the number of parameters defined in the procedure. Adjust the `CALL` statement to match the expected parameter count.

-- Example: If procedure expects 2 parameters, but you called with 1:
-- INCORRECT: CALL your_procedure_name('value1');
-- CORRECT: CALL your_procedure_name('value1', 'value2');

2. Inspect Stored Procedure Definition for Parameter Mismatch medium

Thoroughly examine the stored procedure's definition for any discrepancies in parameter types or order.

1

Use `SHOW CREATE PROCEDURE` to get the exact definition of the problematic stored procedure.

SHOW CREATE PROCEDURE your_procedure_name;

2

Carefully review the `CREATE PROCEDURE` statement. Pay close attention to:
- The number of parameters listed between the parentheses.
- The names and data types of each parameter.
- The order in which parameters are defined.

3

Compare this definition with how you are calling the procedure. Ensure that:
- The number of arguments in the `CALL` statement exactly matches the number of parameters.
- The data types of the arguments are compatible with the defined parameter types.
- The order of arguments in the `CALL` statement corresponds to the order of parameters in the definition.

-- Example: Procedure definition:
-- CREATE PROCEDURE my_proc(IN p1 INT, IN p2 VARCHAR(50))
-- 
-- Incorrect call (wrong order):
-- CALL my_proc('some_string', 123);
-- 
-- Correct call:
-- CALL my_proc(123, 'some_string');

4

If necessary, modify the `CALL` statement to align perfectly with the stored procedure's parameter signature.

3. Recreate Stored Procedure with Correct Parameters medium

If the procedure definition is incorrect or outdated, redefine it with the expected parameter signature.

1

Back up the existing stored procedure definition. This is a safety measure in case of errors during recreation.

SHOW CREATE PROCEDURE your_procedure_name;

2

Drop the existing stored procedure.

DROP PROCEDURE IF EXISTS your_procedure_name;

3

Create the stored procedure again, ensuring the parameter list is accurate and matches how you intend to call it. Pay close attention to the number, order, and data types of parameters.

DELIMITER //
CREATE PROCEDURE your_procedure_name(
    IN param1 INT,
    IN param2 VARCHAR(100),
    OUT result_param VARCHAR(255)
)
BEGIN
    -- Procedure logic here
    SET result_param = CONCAT('Processed: ', param1, ' - ', param2);
END //
DELIMITER ;

4

Now, call the procedure with the correct number and types of arguments.

SET @output_var;
CALL your_procedure_name(10, 'test data', @output_var);
SELECT @output_var;

🔗

Related Errors

5 related errors

MariaDB Error 1107: Incorrect Stored Procedure Parameters