📋

Description

MariaDB Error 1330, 'Duplicate parameter: %s', signifies that a stored procedure, function, or trigger definition contains multiple parameters with the same name. This error prevents the successful creation or alteration of the routine, as parameter names must be unique within their scope.

💬

Error Message

Duplicate parameter: %s

🔍

Known Causes

3 known causes

⚠️

Redundant Parameter Naming

Accidentally defining two or more parameters with identical names within the same `CREATE PROCEDURE` or `CREATE FUNCTION` statement.

⚠️

Copy-Paste Errors in Routine Definition

When copying and pasting code blocks for parameter definitions, an existing parameter name might be inadvertently reused without modification.

⚠️

Conflicting Parameter Naming

Attempting to define a parameter with the same name as another parameter, even if case sensitivity rules might vary, leading to a naming conflict.

🛠️

Solutions

3 solutions available

1. Identify and Rename Duplicate Parameters easy

Find and rename parameters with identical names within the stored routine definition.

1

Examine the `CREATE PROCEDURE` or `CREATE FUNCTION` statement that is failing. Look for any parameters that have the exact same name.

SHOW CREATE PROCEDURE your_procedure_name;
SHOW CREATE FUNCTION your_function_name;

2

Once identified, rename one or more of the duplicate parameters to be unique. Ensure the new names are descriptive and follow your naming conventions.

CREATE PROCEDURE your_procedure_name(
    IN param1 INT,
    IN param2 VARCHAR(255),
    IN param3 INT -- Renamed from original duplicate
)
BEGIN
    -- Routine logic
END;

3

If the routine already exists, you'll need to `DROP` and `CREATE` it again with the corrected parameter names.

DROP PROCEDURE IF EXISTS your_procedure_name;

CREATE PROCEDURE your_procedure_name(
    IN param1 INT,
    IN param2 VARCHAR(255),
    IN param3 INT
)
BEGIN
    -- Routine logic
END;

2. Review Stored Routine Definition in Database medium

Query the information schema to find the stored routine and inspect its parameters directly.

1

Query the `INFORMATION_SCHEMA.PARAMETERS` table to list all parameters for your stored routine. Filter by `SPECIFIC_NAME` (the routine name) and `SPECIFIC_SCHEMA` (the database name).

SELECT PARAMETER_NAME, ORDINAL_POSITION, PARAMETER_MODE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'your_procedure_name'
  AND SPECIFIC_SCHEMA = 'your_database_name'
ORDER BY ORDINAL_POSITION;

2

Analyze the output for any `PARAMETER_NAME` entries that are identical. The `ORDINAL_POSITION` will help you distinguish them if they appear multiple times.

/* Example output showing duplicate parameter names */
-- PARAMETER_NAME | ORDINAL_POSITION | PARAMETER_MODE
-- param_id       | 1                | IN
-- data_value     | 2                | IN
-- param_id       | 3                | OUT

3

Modify the `CREATE PROCEDURE` or `CREATE FUNCTION` statement, or use `ALTER PROCEDURE` if supported for parameter renaming (though it's often easier to drop and recreate), to ensure all parameter names are unique.

DROP PROCEDURE IF EXISTS your_procedure_name;

CREATE PROCEDURE your_procedure_name(
    IN param_id INT,
    IN data_value VARCHAR(255),
    OUT output_status INT -- Renamed from a duplicate
)
BEGIN
    -- Routine logic
END;

3. Re-evaluate Routine Logic and Parameter Usage advanced

Consider if the duplicate parameter is intentional and if the routine's design needs adjustment.

1

Carefully review the stored routine's purpose. Determine if having two parameters with the same name was an oversight or if there's a conceptual misunderstanding in how the routine is intended to be used.

/* No direct code snippet, this is a design review step */

2

If the duplicate parameter is genuinely needed, consider if different data types or `INOUT`/`OUT` modes can differentiate them. If not, a redesign might be necessary.

/* Example: If 'user_id' is needed as both input and output, consider naming them distinctly */
CREATE PROCEDURE update_user_status(
    IN user_id_in INT,
    IN new_status VARCHAR(50),
    OUT user_id_out INT -- Or a different name for the output parameter
)
BEGIN
    -- Logic to update status and potentially return the user ID
    SET user_id_out = user_id_in;
END;

3

Implement the corrected routine definition with unique parameter names or a revised parameter structure.

DROP PROCEDURE IF EXISTS update_user_status;

CREATE PROCEDURE update_user_status(
    IN user_id_input INT,
    IN new_status VARCHAR(50),
    OUT processed_user_id INT
)
BEGIN
    -- Routine logic
    SET processed_user_id = user_id_input;
END;

🔗

Related Errors

5 related errors

MariaDB Error 1330: Duplicate Stored Routine Parameter