Procedure parameters (IN, OUT, INOUT) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a procedure with IN and OUT parameters

Consider the following MySQL procedure:

DELIMITER $$
CREATE PROCEDURE CalculateSum(IN a INT, IN b INT, OUT sum_result INT)
BEGIN
  SET sum_result = a + b;
END$$
DELIMITER ;

If you call this procedure with a=5 and b=7, what will be the value of sum_result after execution?

MySQL

CALL CalculateSum(5, 7, @sum_result);
SELECT @sum_result;

A 35

B 12

C NULL

D Error: sum_result is not set

💡 Hint

OUT parameters are used to return values from procedures.

The procedure adds the two input parameters 5 and 7, then sets the OUT parameter sum_result to 12. Selecting @sum_result returns 12.

❓ query_result

intermediate

Behavior of INOUT parameter in MySQL procedure

Given this procedure:

DELIMITER $$
CREATE PROCEDURE IncrementValue(INOUT val INT)
BEGIN
  SET val = val + 1;
END$$
DELIMITER ;

If you execute:
SET @x = 10;
CALL IncrementValue(@x);
SELECT @x;

What is the output of SELECT @x;?

MySQL

SET @x = 10;
CALL IncrementValue(@x);
SELECT @x;

A 11

B 10

C NULL

D Error: val is not initialized

💡 Hint

INOUT parameters can be used to pass values in and get updated values out.

The procedure receives val=10, increments it by 1, and updates the variable @x to 11.

📝 Syntax

advanced

Identify the syntax error in procedure parameter declaration

Which of the following procedure declarations is syntactically correct in MySQL?

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

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

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

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

💡 Hint

Parameter modes come before the data type in MySQL.

In MySQL, parameter modes (IN, OUT, INOUT) must precede the data type. Option D follows this rule and has a proper BEGIN...END block.

🔧 Debug

advanced

Why does this procedure call fail?

Given the procedure:

DELIMITER $$
CREATE PROCEDURE Multiply(IN x INT, IN y INT, OUT product INT)
BEGIN
  SET product = x * y;
END$$
DELIMITER ;

You run:
CALL Multiply(3, 4, 12);

Why does this call fail?

MySQL

CALL Multiply(3, 4, 12);

A Because OUT parameters must be passed as variables, not constants

B Because the procedure requires only two parameters

C Because the procedure does not have an OUT parameter

D Because the procedure must be called with named parameters

💡 Hint

OUT parameters require a variable to store the output value.

You cannot pass a constant like 12 to an OUT parameter; it must be a user variable (e.g., @product) to receive the output.

🧠 Conceptual

expert

Effect of IN, OUT, and INOUT parameters on variable values

Suppose you have these variables:

SET @a = 5;
SET @b = 10;
SET @c = 15;

And this procedure:

DELIMITER $$
CREATE PROCEDURE TestParams(IN x INT, OUT y INT, INOUT z INT)
BEGIN
  SET y = x + z;
  SET z = z + 5;
END$$
DELIMITER ;

After executing:

CALL TestParams(@a, @b, @c);
SELECT @a, @b, @c;

What is the output of SELECT @a, @b, @c;?

MySQL

SET @a = 5;
SET @b = 10;
SET @c = 15;
CALL TestParams(@a, @b, @c);
SELECT @a, @b, @c;

A 5, 15, 20

B 5, 20, 15

C 5, 20, 20

D 5, 10, 15

💡 Hint

IN parameters do not change the original variable; OUT and INOUT can update variables.

x is IN so @a stays 5; y is OUT and set to x+z = 5+15=20, so @b becomes 20; z is INOUT, initially 15, then incremented by 5 to 20, so @c becomes 20.

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