Procedure Parameters: IN, OUT, and INOUT0%

Control Flow: Variables, Branches & Loops

Procedure Parameters: IN, OUT, and INOUT

Advanced14 min read•Updated: 2026-09-12

Procedure Parameters: IN, OUT, and INOUT

Stored procedures become truly versatile when they accept inputs and return calculated outputs dynamically. MySQL procedures support three parameter modes: IN, OUT, and INOUT.

The Three Parameter Modes Explained

1
IN (Default): Pass-by-value input parameter. The procedure receives a copy of the caller's value. The procedure can modify the variable locally, but changes are not visible to the caller outside the procedure.
2
OUT: Pass-by-reference output parameter. The procedure initializes the parameter to NULL and assigns a value to it that is passed back to the caller upon completion.
3
INOUT: Dual-purpose parameter. The caller provides an initial input value, and the procedure can inspect, modify, and pass the updated value back to the caller.

Syntax Specification

1CREATE PROCEDURE proc_name(
2    [IN | OUT | INOUT] param1_name datatype,
3    [IN | OUT | INOUT] param2_name datatype
4)

Comprehensive Example Demonstrating All Three Modes

Let's build an order fulfillment procedure that takes an IN order ID, computes an OUT total price, and updates an INOUT customer reward points balance:

1DELIMITER //
2 
3CREATE PROCEDURE ProcessOrderReward(
4    IN  p_order_id INT,               -- Input order to evaluate
5    OUT p_total_amount DECIMAL(10,2), -- Output total computed
6    INOUT p_reward_points INT         -- Points input and updated!
7)
8BEGIN
9    -- 1. Retrieve order total into the OUT parameter
10    SELECT total_amount 
11    INTO p_total_amount
12    FROM orders
13    WHERE order_id = p_order_id;
14 
15    -- 2. Award 1 point for every $10 spent, adding to existing points
16    SET p_reward_points = p_reward_points + FLOOR(p_total_amount / 10);
17END //
18 
19DELIMITER ;

Calling Procedures with Session Variables

To receive values from OUT and INOUT parameters, pass MySQL session user variables (prefixed with @):

1-- 1. Initialize user variable for INOUT parameter
2SET @current_points = 50;
3 
4-- 2. Call procedure passing literal for IN, and @variables for OUT and INOUT
5CALL ProcessOrderReward(1001, @calculated_total, @current_points);
6 
7-- 3. Inspect returned output values
8SELECT 
9    @calculated_total AS final_order_total,
10    @current_points AS updated_points_balance;

Execution Result:

If order 1001 had total_amount = 250.00:

@calculated_total is populated with 250.00.
@current_points increases from 50 by FLOOR(250 / 10) = 25, yielding 75!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the default mode for a stored procedure parameter if neither IN, OUT, nor INOUT is specified?

Question 2

What initial value does an OUT parameter hold when the procedure execution begins?

Question 3

Which parameter mode allows a variable to be passed in with an initial value, modified inside the routine, and returned with the new value?

Question 4

How are MySQL session user variables denoted when passed to receive OUT values?

Question 5

Can an IN parameter be modified inside the procedure to change the caller's outer variable?

Next Lesson

Control Flow: Variables, Branches & Loops

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Defining Stored Procedures (DELIMITER //, CREATE PROCEDURE)	Control Flow: Variables, Branches & Loops

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Control Flow: Variables, Branches & Loops

Procedure Parameters: IN, OUT, and INOUT

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Procedure Parameters: IN, OUT, and INOUT

Stored procedures become truly versatile when they accept inputs and return calculated outputs dynamically. MySQL procedures support three parameter modes: IN, OUT, and INOUT.

The Three Parameter Modes Explained

1
IN (Default): Pass-by-value input parameter. The procedure receives a copy of the caller's value. The procedure can modify the variable locally, but changes are not visible to the caller outside the procedure.
2
OUT: Pass-by-reference output parameter. The procedure initializes the parameter to NULL and assigns a value to it that is passed back to the caller upon completion.
3
INOUT: Dual-purpose parameter. The caller provides an initial input value, and the procedure can inspect, modify, and pass the updated value back to the caller.

Syntax Specification

1CREATE PROCEDURE proc_name(
2    [IN | OUT | INOUT] param1_name datatype,
3    [IN | OUT | INOUT] param2_name datatype
4)

Comprehensive Example Demonstrating All Three Modes

Let's build an order fulfillment procedure that takes an IN order ID, computes an OUT total price, and updates an INOUT customer reward points balance:

1DELIMITER //
2 
3CREATE PROCEDURE ProcessOrderReward(
4    IN  p_order_id INT,               -- Input order to evaluate
5    OUT p_total_amount DECIMAL(10,2), -- Output total computed
6    INOUT p_reward_points INT         -- Points input and updated!
7)
8BEGIN
9    -- 1. Retrieve order total into the OUT parameter
10    SELECT total_amount 
11    INTO p_total_amount
12    FROM orders
13    WHERE order_id = p_order_id;
14 
15    -- 2. Award 1 point for every $10 spent, adding to existing points
16    SET p_reward_points = p_reward_points + FLOOR(p_total_amount / 10);
17END //
18 
19DELIMITER ;

Calling Procedures with Session Variables

To receive values from OUT and INOUT parameters, pass MySQL session user variables (prefixed with @):

1-- 1. Initialize user variable for INOUT parameter
2SET @current_points = 50;
3 
4-- 2. Call procedure passing literal for IN, and @variables for OUT and INOUT
5CALL ProcessOrderReward(1001, @calculated_total, @current_points);
6 
7-- 3. Inspect returned output values
8SELECT 
9    @calculated_total AS final_order_total,
10    @current_points AS updated_points_balance;

Execution Result:

If order 1001 had total_amount = 250.00:

@calculated_total is populated with 250.00.
@current_points increases from 50 by FLOOR(250 / 10) = 25, yielding 75!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the default mode for a stored procedure parameter if neither IN, OUT, nor INOUT is specified?

Question 2

What initial value does an OUT parameter hold when the procedure execution begins?

Question 3

Which parameter mode allows a variable to be passed in with an initial value, modified inside the routine, and returned with the new value?

Question 4

How are MySQL session user variables denoted when passed to receive OUT values?

Question 5

Can an IN parameter be modified inside the procedure to change the caller's outer variable?

Next Lesson

Control Flow: Variables, Branches & Loops

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Defining Stored Procedures (DELIMITER //, CREATE PROCEDURE)	Control Flow: Variables, Branches & Loops

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next