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.