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.