How to Use IN, OUT, and INOUT Parameters in MySQL Stored Procedures

In MySQL stored procedures, use IN parameters to pass values into the procedure, OUT parameters to return values back to the caller, and INOUT parameters to both receive and return values. Define these parameters in the procedure header and use CALL to execute the procedure, passing variables for OUT and INOUT to get results.

Syntax

MySQL stored procedures can have three types of parameters:

• IN: Input parameter, used to pass a value into the procedure.
• OUT: Output parameter, used to return a value from the procedure.
• INOUT: Input and output parameter, used to pass a value in and get a modified value back.

Parameters are declared in the procedure header like this:

sql

CREATE PROCEDURE procedure_name(
  IN param_in INT,
  OUT param_out INT,
  INOUT param_inout INT
)
BEGIN
  -- procedure body
END

Example

This example shows a procedure that takes an IN parameter, modifies an INOUT parameter, and sets an OUT parameter. It demonstrates how to call the procedure and get the output values.

sql

DELIMITER $$
CREATE PROCEDURE example_proc(
  IN input_val INT,
  OUT output_val INT,
  INOUT inout_val INT
)
BEGIN
  SET output_val = input_val * 2;
  SET inout_val = inout_val + input_val;
END$$
DELIMITER ;

-- Declare variables to hold output
SET @out_var = 0;
SET @inout_var = 5;

-- Call the procedure
CALL example_proc(10, @out_var, @inout_var);

-- Check results
SELECT @out_var AS output_value, @inout_var AS inout_value;

Output

output_value | inout_value
------------ | -----------
20           | 15

Common Pitfalls

Common mistakes when using IN, OUT, and INOUT parameters include:

• Not using variables for OUT and INOUT parameters when calling the procedure. You must pass variables, not constants.
• Confusing INOUT with OUT. INOUT requires an initial value; OUT does not.
• Forgetting to use DELIMITER when creating procedures with multiple statements.

Example of wrong and right call:

sql

-- Wrong: passing constant for OUT parameter
CALL example_proc(10, 0, @inout_var); -- This will cause error

-- Right: passing variable for OUT parameter
SET @out_var = 0;
CALL example_proc(10, @out_var, @inout_var);

Quick Reference

Parameter Type	Purpose	Usage in CALL
IN	Pass value into procedure	Pass constant or variable
OUT	Return value from procedure	Pass variable to receive value
INOUT	Pass value in and get modified value back	Pass variable with initial value

Key Takeaways

Use IN parameters to send data into a stored procedure.

Use OUT parameters to get data back from a procedure via variables.

Use INOUT parameters to send and receive data through the same variable.

Always pass variables (not constants) for OUT and INOUT parameters when calling procedures.

Use DELIMITER to define procedures with multiple statements.