How to Pass Parameters to Procedures in MySQL

In MySQL, you pass parameters to a stored procedure by defining them in the procedure declaration using IN, OUT, or INOUT keywords. When calling the procedure, you provide the arguments in the same order as the parameters.

Syntax

To pass parameters to a MySQL procedure, you declare them inside the parentheses after the procedure name. Use IN for input parameters, OUT for output parameters, and INOUT for parameters that can be both input and output.

• IN param_name datatype: Passes a value into the procedure.
• OUT param_name datatype: Returns a value from the procedure.
• INOUT param_name datatype: Passes a value in and returns a modified value.

sql

CREATE PROCEDURE procedure_name(
  IN param1 INT,
  OUT param2 VARCHAR(50),
  INOUT param3 DATE
)
BEGIN
  -- procedure body
END

Example

This example shows a procedure that takes an input parameter and returns an output parameter. It calculates the length of a given string.

sql

DELIMITER $$
CREATE PROCEDURE GetStringLength(
  IN inputStr VARCHAR(100),
  OUT length INT
)
BEGIN
  SET length = CHAR_LENGTH(inputStr);
END$$
DELIMITER ;

-- Call the procedure
CALL GetStringLength('Hello, MySQL!', @len);

-- Check the output
SELECT @len AS StringLength;

Output

StringLength
13

Common Pitfalls

Common mistakes when passing parameters to MySQL procedures include:

• Not using the correct IN, OUT, or INOUT keyword in the procedure definition.
• Forgetting to use user-defined variables (like @var) to capture OUT or INOUT parameter values when calling the procedure.
• Passing parameters in the wrong order.
• Not setting the DELIMITER properly when creating procedures with multiple statements.

Wrong way:

CALL GetStringLength('Hello', length); -- 'length' is not a variable

Right way:

CALL GetStringLength('Hello', @length);
SELECT @length;

Quick Reference

Keyword	Description	Usage
IN	Input parameter	Pass value into procedure
OUT	Output parameter	Return value from procedure
INOUT	Input and output parameter	Pass value in and return modified value

Key Takeaways

Define parameters with IN, OUT, or INOUT keywords in the procedure declaration.

Use user variables (e.g., @var) to receive OUT or INOUT parameter values when calling procedures.

Pass parameters in the exact order they are declared.

Set DELIMITER properly when creating procedures with multiple statements.

Always test procedure calls to ensure parameters are passed and returned correctly.