📋

Description

MySQL Error 1330, 'Duplicate parameter: %s', indicates that you have defined a stored procedure, function, or trigger with two or more input parameters (arguments) that share the exact same name. This error prevents the routine from being created or altered successfully, as parameter names must be unique within the same routine definition.

💬

Error Message

Duplicate parameter: %s

🔍

Known Causes

3 known causes

⚠️

Accidental Reuse of Parameter Name

When defining or modifying a routine, a parameter name might be inadvertently used more than once in the parameter list.

⚠️

Copy-Paste Errors

Copying and pasting sections of code, especially parameter definitions, without renaming parameters can introduce duplicates.

⚠️

Refactoring Oversight

During the refactoring of a database routine, a parameter name might be changed in one place but not consistently updated across all instances, leading to a duplicate.

🛠️

Solutions

3 solutions available

1. Rename Duplicate Stored Procedure/Function Parameters easy

Identify and rename identical parameter names within the stored procedure or function definition.

1

Locate the stored procedure or function that is causing the error. The error message usually indicates the name of the object. You can use `SHOW CREATE PROCEDURE procedure_name;` or `SHOW CREATE FUNCTION function_name;` to see the exact definition.

SHOW CREATE PROCEDURE my_procedure_name;
-- OR --
SHOW CREATE FUNCTION my_function_name;

2

Examine the parameter list in the `CREATE PROCEDURE` or `CREATE FUNCTION` statement. Identify any parameters that share the same name.

-- Example of a problematic definition:
CREATE PROCEDURE my_procedure (IN param1 INT, IN param1 VARCHAR(50), OUT result INT)

3

Rename one or more of the duplicate parameters to be unique. It's good practice to use descriptive names.

-- Corrected example:
CREATE PROCEDURE my_procedure (IN input_id INT, IN input_name VARCHAR(50), OUT result INT)

4

Drop the existing procedure/function and recreate it with the corrected parameter names.

DROP PROCEDURE IF EXISTS my_procedure_name;
-- OR --
DROP FUNCTION IF EXISTS my_function_name;

-- Then execute the CREATE PROCEDURE/FUNCTION statement with unique parameter names.

2. Review and Correct Dynamic SQL Parameter Names medium

If the error occurs within dynamic SQL, ensure that the parameter names used in `PREPARE` and `EXECUTE` statements are unique.

1

Identify the stored procedure or trigger where dynamic SQL is being used. Look for `PREPARE`, `EXECUTE`, and `DEALLOCATE PREPARE` statements.

SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%PREPARE%';

2

Examine the `PREPARE` statement. Ensure that all named parameters (if any) within the SQL string being prepared are unique. Also, check the `EXECUTE` statement for duplicate user-defined variable names if they are being passed as parameters.

-- Example of problematic dynamic SQL:
SET @sql = 'SELECT * FROM my_table WHERE col1 = @val1 AND col2 = @val1';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @val1, @val1; -- This is fine if @val1 is used once in the EXECUTE clause

-- Problematic EXECUTE with duplicate named placeholders:
SET @sql = 'SELECT * FROM my_table WHERE col1 = ? AND col2 = ?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @value1, @value1; -- This is fine as it maps to the ? placeholders

-- The error is more likely when the *named parameters* within the SQL string itself are duplicated, or when the *EXECUTE USING* clause tries to pass the same user-defined variable for multiple distinct placeholders implicitly.

3

Rename any duplicate named parameters within the dynamic SQL string or ensure that distinct user-defined variables are used for each parameter in the `EXECUTE USING` clause.

-- Corrected example:
SET @sql = 'SELECT * FROM my_table WHERE col1 = @first_val AND col2 = @second_val';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @first_val, @second_val;

-- Or if using placeholders:
SET @sql = 'SELECT * FROM my_table WHERE col1 = ? AND col2 = ?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @value1, @value2;

4

If the dynamic SQL is part of a stored procedure, update the procedure definition with the corrected dynamic SQL.

ALTER PROCEDURE my_procedure_name ... ;

3. Examine Event Scheduler and Trigger Definitions medium

Check for duplicate parameter names in stored procedures or functions called by Event Scheduler or triggers.

1

Identify any Event Scheduler jobs or triggers that call stored procedures or functions.

-- For Event Scheduler:
SHOW EVENTS;

-- For Triggers:
SHOW TRIGGERS;

2

For each identified Event Scheduler job or trigger, inspect the stored procedure or function it calls. Use `SHOW CREATE PROCEDURE procedure_name;` or `SHOW CREATE FUNCTION function_name;`.

SHOW CREATE PROCEDURE event_called_procedure;
-- OR --
SHOW CREATE FUNCTION trigger_called_function;

3

Review the parameter list of the called procedure/function for duplicate names, as described in Solution 1.

-- Example of a problematic definition:
CREATE PROCEDURE process_data (IN data_id INT, IN data_id VARCHAR(100))

4

Rename the duplicate parameters to ensure uniqueness and then recreate the stored procedure or function.

-- Corrected example:
CREATE PROCEDURE process_data (IN record_id INT, IN record_identifier VARCHAR(100))

🔗

Related Errors

5 related errors

MySQL Error 1330: Duplicate Parameter Name