Master MySQL Stored Procedure Parameters: IN, OUT, INOUT Explained with Real Examples

Storage procedure beginner series focuses on one concept per article, with practical examples and clear step‑by‑step guidance.

Why are stored procedure parameters so important?

Hard‑coding values like a fixed name in a procedure forces you to modify and redeploy the code each time the name changes, which is inefficient and error‑prone.

Writing a procedure with hard‑coded values such as "张三" requires code changes whenever you need "李四", leading to repeated modifications and deployments.

Parameters act as input ports for procedures, making them flexible and reusable.

Environment

MySQL 8.0.44

DataGrip client for executing SQL

Test database:

testdb

1. Recognize IN, OUT, INOUT parameters

IN : Input parameter, passed in at call time, read‑only inside the procedure.

OUT : Output parameter, not passed in, assigned inside the procedure and returned to the caller.

INOUT : Input‑output parameter, passed in with an initial value, can be read and modified inside the procedure, and the final value is returned.

2. Basic procedure with an IN parameter

Requirement: Pass a name to the procedure and return a welcome message.

DELIMITER //
CREATE PROCEDURE welcome_in(IN user_name VARCHAR(50))
BEGIN
  SELECT CONCAT('欢迎你，', user_name, '，关注 IT咸鱼！') AS welcome_msg;
END //
DELIMITER ;

Explanation: The IN user_name VARCHAR(50) defines an input parameter; SELECT returns the concatenated message as a result set. CALL welcome_in('小明'); Result:

3. Procedure with an OUT parameter

Requirement: Create a procedure that returns a fixed welcome message.

DELIMITER //
CREATE PROCEDURE welcome_out(OUT msg VARCHAR(100))
BEGIN
  SET msg = '欢迎关注 IT咸鱼，和咸鱼一起成长！';
END //
DELIMITER ;

Explanation: The OUT msg VARCHAR(100) defines an output parameter that is assigned inside the procedure.

SET @msg = '';
CALL welcome_out(@msg);
SELECT @msg;

Result:

4. Procedure with an INOUT parameter

Requirement: Modify the passed name by adding a prefix and return it.

DELIMITER //
CREATE PROCEDURE welcome_inout(INOUT user_name VARCHAR(50))
BEGIN
  SET user_name = CONCAT('尊敬的', user_name);
END //
DELIMITER ;

Explanation: The INOUT user_name allows the procedure to read the initial value and write back the modified value.

SET @user_name = '小明';
CALL welcome_inout(@user_name);
SELECT @user_name;

Result:

5. Common questions

Why use DELIMITER? MySQL client uses a semicolon to end statements; changing the delimiter (e.g., to //) allows the whole procedure definition to be parsed correctly.

Where are procedure parameters defined? In the CREATE PROCEDURE procedure_name(parameter_list) clause, specifying name, type, and direction.

What is the scope of parameters? Parameters exist only inside the procedure; after execution they are returned to the caller via result sets or output parameters.

Difference between variables and parameters? Parameters are the procedure’s interface for input/output, while variables are temporary storage used inside the procedure.

Why call with CALL? Stored procedures run on the server and must be invoked with the CALL command.

6. Execution environment

Procedures are stored on the MySQL server (in the mysql.proc table).

The client (e.g., DataGrip) sends CALL commands to execute them.

Input parameters are sent from the client; the procedure can return result sets and/or output parameters.

Procedures are black‑box code; you can view the definition with SHOW CREATE PROCEDURE.

7. Advanced exercise

Write a procedure that receives a username and age, determines if the user is an adult, and returns an appropriate message.

DELIMITER //
CREATE PROCEDURE check_age(
  IN user_name VARCHAR(50),
  IN user_age INT,
  OUT message VARCHAR(200)
)
BEGIN
  IF user_age >= 18 THEN
    SET message = CONCAT(user_name, '，你已经成年了，欢迎关注 IT咸鱼！');
  ELSE
    SET message = CONCAT(user_name, '，你未成年，注意保护自己！');
  END IF;
END //
DELIMITER ;

Call example for an adult:

SET @msg = '';
CALL check_age('小红', 20, @msg);
SELECT @msg;

Result:

Call example for a minor:

SET @msg = '';
CALL check_age('小强', 16, @msg);
SELECT @msg;

Result:

8. Summary and learning tips

IN : Input only; read‑only inside the procedure.

OUT : Output only; assigned inside and retrieved after execution.

INOUT : Both input and output; can be modified and the new value is returned.

Calling : Always use CALL from the client.

Best practices : Define clear parameter types and lengths; use DELIMITER to avoid parsing errors.