Parameters (IN, OUT, INOUT) in SQL - Step-by-Step Execution

Concept Flow - Parameters (IN, OUT, INOUT)

Start Procedure Call

↓

Read IN Parameters

↓

Execute Procedure Body

↓

Modify OUT and INOUT Parameters

↓

Return to Caller with OUT/INOUT Values

↓

End

The procedure starts by reading IN parameters, executes its body, modifies OUT and INOUT parameters, then returns these values to the caller.

Execution Sample

SQL

CREATE PROCEDURE example_proc(
  IN input_val INT,
  OUT output_val INT,
  INOUT inout_val INT
)
BEGIN
  SET output_val = input_val + 10;
  SET inout_val = inout_val + input_val;
END;

This procedure takes an input, sets an output as input plus 10, and updates an INOUT parameter by adding the input.

Execution Table

Step	Parameter	Initial Value	Action	Value After Action
1	input_val (IN)	5	Read input parameter	5
2	output_val (OUT)	NULL	Initialize output parameter	NULL
3	inout_val (INOUT)	3	Read INOUT parameter	3
4	output_val (OUT)	NULL	Set output_val = input_val + 10	15
5	inout_val (INOUT)	3	Set inout_val = inout_val + input_val	8
6	Return	N/A	Return OUT and INOUT parameters to caller	output_val=15, inout_val=8

💡 Procedure ends after returning modified OUT and INOUT parameters.

Variable Tracker

Parameter	Start	After Step 3	After Step 5	Final
input_val (IN)	5	5	5	5
output_val (OUT)	NULL	NULL	15	15
inout_val (INOUT)	3	3	8	8

Key Moments - 3 Insights

Why does the OUT parameter start as NULL and not have an initial value?

OUT parameters do not receive values from the caller initially; they are meant to be set inside the procedure before returning, as shown in execution_table step 2 and 4.

How is the INOUT parameter different from IN and OUT parameters?

INOUT parameters start with a value from the caller (like IN), can be modified inside the procedure, and the new value is returned (like OUT). This is shown in steps 3 and 5.

Can the procedure modify IN parameters inside its body?

No, IN parameters are read-only inside the procedure. Only OUT and INOUT parameters can be assigned new values, as seen in steps 4 and 5.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of inout_val after step 5?

A 8

B 3

C 5

D 15

💡 Hint

Check the 'Value After Action' column for inout_val at step 5 in the execution_table.

At which step does output_val get its value assigned?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the action 'Set output_val = input_val + 10' in the execution_table.

If input_val was changed to 7, what would output_val be after step 4?

A 10

B 17

C 7

D NULL

💡 Hint

Recall output_val is set to input_val + 10, so check the calculation in step 4.

Concept Snapshot

Parameters in SQL procedures:
- IN: input only, read inside procedure
- OUT: output only, set inside procedure
- INOUT: input and output, read and modified
Procedure reads IN and INOUT at start,
modifies OUT and INOUT,
returns OUT and INOUT to caller.

Full Transcript

This visual execution trace shows how SQL procedure parameters work. The procedure starts by reading IN and INOUT parameters. OUT parameters start as NULL because they are meant to be set inside the procedure. The procedure then sets the OUT parameter to input_val plus 10 and updates the INOUT parameter by adding input_val. Finally, the procedure returns the OUT and INOUT parameters to the caller with their new values. IN parameters cannot be changed inside the procedure. This step-by-step trace helps understand how each parameter type behaves during procedure execution.

Practice

1. What does the IN parameter type in a SQL stored procedure do?

easy

A. It allows the procedure to send output values only.

B. It allows the procedure to receive input values only.

C. It allows the procedure to both receive input and send output.

D. It declares a variable inside the procedure.

Solution

1. Step 1: Understand parameter types in SQL procedures

   Parameters can be IN, OUT, or INOUT. IN means input only.

2. Step 2: Identify the role of IN parameters

   IN parameters pass values into the procedure but do not return values.

3. Final Answer:

   It allows the procedure to receive input values only. -> Option B

4. Quick Check:

   IN = input only [OK]

Hint: IN means input only, no output from procedure [OK]

Common Mistakes:

• Confusing IN with OUT or INOUT
• Thinking IN parameters can return values
• Assuming IN declares internal variables

2. Which of the following is the correct syntax to declare an OUT parameter named result of type INT in a MySQL stored procedure?

easy

A. CREATE PROCEDURE proc_name(IN result INT)

B. CREATE PROCEDURE proc_name(INOUT result INT)

C. CREATE PROCEDURE proc_name(OUT result INT)

D. CREATE PROCEDURE proc_name(RESULT OUT INT)

Solution

1. Step 1: Recall parameter declaration syntax in MySQL

   Parameters are declared as IN, OUT, or INOUT followed by name and type.

2. Step 2: Check each option's syntax

   CREATE PROCEDURE proc_name(OUT result INT) uses correct syntax: OUT result INT. CREATE PROCEDURE proc_name(INOUT result INT) uses INOUT, not OUT. CREATE PROCEDURE proc_name(IN result INT) uses IN, not OUT. CREATE PROCEDURE proc_name(RESULT OUT INT) has wrong order and keyword.

3. Final Answer:

   CREATE PROCEDURE proc_name(OUT result INT) -> Option C

4. Quick Check:

   OUT parameter syntax = OUT name type [OK]

Hint: OUT parameters use OUT before name and type [OK]

Common Mistakes:

• Using IN instead of OUT
• Swapping parameter order
• Using wrong keywords like RESULT OUT

3. Consider this MySQL procedure call:

CALL calculate_sum(5, 10, @total);
SELECT @total;

Given the procedure signature:

CREATE PROCEDURE calculate_sum(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END;

What will be the output of SELECT @total;?

medium

A. 5

B. Syntax error

C. NULL

D. 15

Solution

1. Step 1: Understand procedure parameters and call

   The procedure takes two input numbers and outputs their sum via the OUT parameter sum. The call passes 5 and 10 as inputs, and @total as the OUT variable.

2. Step 2: Calculate the output value

   The procedure sets sum = 5 + 10 = 15. This value is assigned to the session variable @total.

3. Final Answer:

   15 -> Option D

4. Quick Check:

   5 + 10 = 15 [OK]

Hint: OUT parameters update session variables after CALL [OK]

Common Mistakes:

• Expecting NULL because of no SELECT inside procedure
• Confusing IN and OUT parameters
• Not using session variables for OUT

4. You have this procedure:

CREATE PROCEDURE update_value(INOUT val INT)
BEGIN
  SET val = val + 1;
END;

Which of the following calls will correctly update the session variable @x from 10 to 11?

medium

A. CALL update_value(@x);

B. CALL update_value(10);

C. CALL update_value(INOUT @x);

D. CALL update_value(IN @x);

Solution

1. Step 1: Understand INOUT parameter usage

   INOUT parameters require a variable to pass in and receive updated value. Session variables like @x can be used.

2. Step 2: Analyze each call

   CALL update_value(@x); passes @x correctly. CALL update_value(10); passes a literal 10, which is not a variable. CALL update_value(INOUT @x); uses invalid syntax. CALL update_value(IN @x); uses IN keyword, which is incorrect for INOUT.

3. Final Answer:

   CALL update_value(@x); -> Option A

4. Quick Check:

   INOUT needs variable, not literal [OK]

Hint: Use session variables for INOUT parameters [OK]

Common Mistakes:

• Passing literals instead of variables
• Using IN or INOUT keywords in CALL
• Expecting procedure to update literals

5. You want to create a procedure that swaps two integer values using INOUT parameters a and b. Which of the following procedure bodies correctly swaps the values?

hard

A. BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END

B. BEGIN SET a = b; SET b = a; END

C. BEGIN SET a = a + b; SET b = a - b; SET a = a - b; END

D. BEGIN SET a = b; SET b = a + b; END

Solution

1. Step 1: Understand swapping logic with INOUT parameters

   Swapping requires a temporary variable to hold one value while assigning the other.

2. Step 2: Evaluate each option's correctness

   BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END uses a temporary variable to swap correctly. BEGIN SET a = b; SET b = a; END overwrites a before assigning b. BEGIN SET a = a + b; SET b = a - b; SET a = a - b; END uses arithmetic swap but is valid only if no overflow and no NULLs. BEGIN SET a = b; SET b = a + b; END does not swap correctly.

3. Final Answer:

   BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END -> Option A

4. Quick Check:

   Temporary variable swap = correct [OK]

Hint: Use temp variable to swap INOUT parameters safely [OK]

Common Mistakes:

• Overwriting variables without temp
• Using arithmetic swap without checks
• Ignoring NULL or overflow risks