Parameters (IN, OUT, INOUT) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a procedure with IN and OUT parameters

Consider the following SQL procedure that takes an IN parameter and an OUT parameter. What will be the value of the OUT parameter after execution?

Procedure:
CREATE PROCEDURE CalculateSquare(IN input_num INT, OUT output_num INT)
BEGIN
SET output_num = input_num * input_num;
END;

Call:
CALL CalculateSquare(4, @result);
SELECT @result;

SQL

CREATE PROCEDURE CalculateSquare(IN input_num INT, OUT output_num INT)
BEGIN
  SET output_num = input_num * input_num;
END;

CALL CalculateSquare(4, @result);
SELECT @result;

A 4

B 16

C NULL

D 8

💡 Hint

Remember that the OUT parameter is assigned inside the procedure.

The procedure calculates the square of the input number (4 * 4) and assigns it to the OUT parameter, so the result is 16.

❓ query_result

intermediate

Effect of INOUT parameter modification inside procedure

Given the procedure below, what will be the value of the variable @val after calling the procedure?

Procedure:
CREATE PROCEDURE IncrementValue(INOUT val INT)
BEGIN
SET val = val + 10;
END;

Call:
SET @val = 5;
CALL IncrementValue(@val);
SELECT @val;

SQL

CREATE PROCEDURE IncrementValue(INOUT val INT)
BEGIN
  SET val = val + 10;
END;

SET @val = 5;
CALL IncrementValue(@val);
SELECT @val;

A 10

B 5

C 15

D NULL

💡 Hint

INOUT parameters can be changed inside the procedure and the change is reflected outside.

The procedure adds 10 to the input value 5, so the final value of @val is 15.

📝 Syntax

advanced

Identify the syntax error in procedure parameter declaration

Which option contains a syntax error in the declaration of procedure parameters?

A CREATE PROCEDURE TestProc(OUT param1 INT, IN param2 INT) BEGIN END;

B CREATE PROCEDURE TestProc(IN param1 INT, INOUT param2 INT) BEGIN END;

C CREATE PROCEDURE TestProc(IN param1 INT, OUT param2 VARCHAR(20)) BEGIN END;

D CREATE PROCEDURE TestProc(param1 INT, param2 INT) BEGIN END;

💡 Hint

Parameters must specify mode: IN, OUT, or INOUT unless default is IN.

Option D omits the parameter mode keywords (IN, OUT, INOUT), which is invalid syntax in many SQL dialects requiring explicit mode.

🧠 Conceptual

advanced

Understanding parameter modes in stored procedures

Which statement correctly describes the behavior of IN, OUT, and INOUT parameters in stored procedures?

A OUT parameters must be assigned a value inside the procedure to return data.

B IN parameters can be modified inside the procedure and changes are reflected outside.

C INOUT parameters cannot be used to pass initial values into the procedure.

D OUT parameters are read-only inside the procedure.

💡 Hint

Think about which parameters are used to send data back to the caller.

OUT parameters are used to return values from the procedure and must be assigned inside it. IN parameters are read-only, and INOUT parameters can be read and modified.

🔧 Debug

expert

Why does this procedure call fail?

Given the procedure and call below, why does the call fail?

Procedure:
CREATE PROCEDURE UpdateValue(INOUT val INT)
BEGIN
SET val = val * 2;
END;

Call:
CALL UpdateValue(10);

SQL

CREATE PROCEDURE UpdateValue(INOUT val INT)
BEGIN
  SET val = val * 2;
END;

CALL UpdateValue(10);

A INOUT parameters require a variable, not a constant, to be passed.

B INOUT parameters cannot be used with numeric types.

C The procedure call syntax is missing a semicolon.

D The procedure must use OUT instead of INOUT for this call.

💡 Hint

Consider what kind of argument can be passed to an INOUT parameter.

INOUT parameters require a variable to hold the value because the procedure can modify it. Passing a constant like 10 is invalid and causes an error.