Procedure parameters (IN, OUT, INOUT) in MySQL - Step-by-Step Execution

Concept Flow - Procedure parameters (IN, OUT, INOUT)

Start Procedure Call

↓

Pass IN parameter value

↓

Procedure executes using IN value

↓

Modify OUT and INOUT parameters inside procedure

↓

Return OUT and INOUT values to caller

↓

End Procedure Call

This flow shows how IN parameters are passed in, OUT and INOUT parameters are modified inside the procedure, and then returned to the caller.

Execution Sample

MySQL

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

This procedure takes an IN parameter, modifies OUT and INOUT parameters based on it.

Execution Table

Step	Parameter	Initial Value	Action	Value After Action
1	in_val	5 (passed in)	Use as input	5
2	out_val	NULL	Set to in_val + 10	15
3	inout_val	3 (passed in)	Add in_val to inout_val	8
4	Procedure ends	-	Return out_val and inout_val to caller	-

💡 Procedure ends after setting OUT and INOUT parameters; values returned to caller.

Variable Tracker

Parameter	Start	After Step 2	After Step 3	Final
in_val	5	5	5	5
out_val	NULL	15	15	15
inout_val	3	3	8	8

Key Moments - 3 Insights

Why can't we change the IN parameter inside the procedure?

IN parameters are read-only inside the procedure, so their value stays the same as shown in execution_table step 1.

How do OUT parameters get their value back to the caller?

OUT parameters are assigned inside the procedure (step 2) and their final value is returned after the procedure ends (step 4).

What happens to INOUT parameters during execution?

INOUT parameters start with a value from the caller (step 1), can be read and changed inside the procedure (step 3), and the updated value is returned (step 4).

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of out_val after step 2?

A 15

B 5

C NULL

D 8

💡 Hint

Check the 'Value After Action' column for out_val at step 2 in the execution_table.

At which step does inout_val change its value?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column for inout_val in the execution_table to see when it is modified.

If the initial in_val was 7 instead of 5, what would out_val be after step 2?

A 7

B 12

C 17

D NULL

💡 Hint

out_val is set to in_val + 10 at step 2; check execution_table logic.

Concept Snapshot

Procedure parameters in MySQL:
- IN: input only, cannot be changed inside procedure.
- OUT: output only, set inside procedure and returned.
- INOUT: input and output, can be read and changed.
Use SET inside procedure to assign OUT and INOUT values.
Caller receives OUT and INOUT updated values after execution.

Full Transcript

This visual execution shows how MySQL procedure parameters work. The IN parameter is passed in and used as input without change. The OUT parameter starts null and is assigned a new value inside the procedure. The INOUT parameter starts with a value, is modified inside, and the new value is returned. The execution table tracks each step and parameter value changes. Key moments clarify common confusions about parameter roles. The quiz tests understanding of parameter values at different steps. The snapshot summarizes the behavior of IN, OUT, and INOUT parameters in procedures.

Practice

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

easy

A. It declares a variable inside the procedure without passing values.

B. It passes a value into the procedure but does not allow changes to be sent back.

C. It allows both passing a value in and sending a changed value back.

D. It allows the procedure to send a value back to the caller only.

Solution

1. Step 1: Understand the role of IN parameters

   IN parameters are used to pass values into a procedure. They act like input-only variables.

2. Step 2: Clarify output behavior of IN parameters

   Changes to IN parameters inside the procedure do not affect the caller's variables; they are input-only.

3. Final Answer:

   It passes a value into the procedure but does not allow changes to be sent back. -> Option B

4. Quick Check:

   IN = input only [OK]

Hint: IN means input only, no output changes sent back [OK]

Common Mistakes:

• Thinking IN parameters can send data back
• Confusing IN with OUT or INOUT
• Assuming IN parameters can be modified outside

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

easy

A. CREATE PROCEDURE myProc(param OUT INT) BEGIN END;

B. CREATE PROCEDURE myProc(INOUT param INT) BEGIN END;

C. CREATE PROCEDURE myProc(OUT param INT) BEGIN END;

D. CREATE PROCEDURE myProc(param INT OUT) BEGIN END;

Solution

1. Step 1: Recall correct parameter declaration order

   In MySQL, the parameter mode (IN, OUT, INOUT) comes before the parameter name and type.

2. Step 2: Check each option's syntax

   CREATE PROCEDURE myProc(OUT param INT) BEGIN END; uses 'OUT param INT' which is correct. Placing OUT after the parameter name or type is invalid syntax.

3. Final Answer:

   CREATE PROCEDURE myProc(OUT param INT) BEGIN END; -> Option C

4. Quick Check:

   Parameter mode before name and type [OK]

Hint: Parameter mode (IN/OUT/INOUT) always comes first [OK]

Common Mistakes:

• Placing OUT after parameter name or type
• Mixing parameter mode with data type
• Omitting BEGIN and END block

3. Consider this procedure call and definition:

CREATE PROCEDURE addValues(IN a INT, INOUT b INT, OUT c INT)
BEGIN
  SET b = b + a;
  SET c = a + b;
END;

CALL addValues(5, @varB, @varC);

If @varB is initially 10, what will be the values of @varB and @varC after the call?

medium

A. @varB = 15, @varC = 20

B. @varB = 10, @varC = 15

C. @varB = 5, @varC = 10

D. @varB = 15, @varC = 10

Solution

1. Step 1: Understand parameter roles and initial values

   a = 5 (IN), b = 10 (INOUT), c is OUT (initially NULL). INOUT means b can be read and updated.

2. Step 2: Calculate new values inside procedure

   SET b = b + a = 10 + 5 = 15; SET c = a + b = 5 + 15 = 20.

3. Final Answer:

   @varB = 15, @varC = 20 -> Option A

4. Quick Check:

   INOUT b updated, OUT c set correctly [OK]

Hint: INOUT changes persist; OUT must be set inside procedure [OK]

Common Mistakes:

• Assuming INOUT parameter does not change
• Confusing OUT parameter initial value
• Ignoring that OUT must be assigned inside procedure

4. Given this procedure:

CREATE PROCEDURE testProc(INOUT x INT)
BEGIN
  SET x = x + 1;
END;

Which of the following calls will cause an error?

medium

A. CALL testProc(@varX); -- @varX is a session variable

B. CALL testProc(NULL);

C. SET @varX = 10; CALL testProc(@varX);

D. CALL testProc(5);

Solution

1. Step 1: Understand INOUT parameter requirements

   INOUT parameters require a variable to pass by reference, so the procedure can read and modify it.

2. Step 2: Analyze each call

   CALL testProc(5); passes a literal (5), which is not a variable, causing an error. The other options pass variables or NULL variables, which are allowed.

3. Final Answer:

   CALL testProc(5); -> Option D

4. Quick Check:

   INOUT needs variable, not literal [OK]

Hint: INOUT requires variable, not constant literal [OK]

Common Mistakes:

• Passing literals to INOUT parameters
• Confusing INOUT with IN parameters
• Assuming NULL literal is invalid for INOUT

5. You want to create a procedure that takes two numbers, adds them, and returns the sum and the product. Which parameter types should you use for the inputs and outputs?

hard

A. IN for both inputs, OUT for sum and product outputs

B. INOUT for inputs, IN for outputs

C. OUT for inputs, IN for outputs

D. IN for inputs, INOUT for outputs

Solution

1. Step 1: Identify input and output roles

   The two numbers are inputs only, so they should be IN parameters. The sum and product are results to send back, so they should be OUT parameters.

2. Step 2: Match parameter types to roles

   IN parameters accept input values; OUT parameters return output values. INOUT is for parameters used as both input and output, which is not needed here.

3. Final Answer:

   IN for both inputs, OUT for sum and product outputs -> Option A

4. Quick Check:

   Inputs = IN, Outputs = OUT [OK]

Hint: Use IN for inputs, OUT for outputs to separate roles clearly [OK]

Common Mistakes:

• Using INOUT unnecessarily for outputs
• Confusing OUT as input
• Using IN for output parameters