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.

Practice

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

easy

A. It allows the procedure to send output values only.

B. It allows the procedure to receive input values only.

C. It allows the procedure to both receive input and send output.

D. It declares a variable inside the procedure.

Solution

1. Step 1: Understand parameter types in SQL procedures

   Parameters can be IN, OUT, or INOUT. IN means input only.

2. Step 2: Identify the role of IN parameters

   IN parameters pass values into the procedure but do not return values.

3. Final Answer:

   It allows the procedure to receive input values only. -> Option B

4. Quick Check:

   IN = input only [OK]

Hint: IN means input only, no output from procedure [OK]

Common Mistakes:

• Confusing IN with OUT or INOUT
• Thinking IN parameters can return values
• Assuming IN declares internal variables

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

easy

A. CREATE PROCEDURE proc_name(IN result INT)

B. CREATE PROCEDURE proc_name(INOUT result INT)

C. CREATE PROCEDURE proc_name(OUT result INT)

D. CREATE PROCEDURE proc_name(RESULT OUT INT)

Solution

1. Step 1: Recall parameter declaration syntax in MySQL

   Parameters are declared as IN, OUT, or INOUT followed by name and type.

2. Step 2: Check each option's syntax

   CREATE PROCEDURE proc_name(OUT result INT) uses correct syntax: OUT result INT. CREATE PROCEDURE proc_name(INOUT result INT) uses INOUT, not OUT. CREATE PROCEDURE proc_name(IN result INT) uses IN, not OUT. CREATE PROCEDURE proc_name(RESULT OUT INT) has wrong order and keyword.

3. Final Answer:

   CREATE PROCEDURE proc_name(OUT result INT) -> Option C

4. Quick Check:

   OUT parameter syntax = OUT name type [OK]

Hint: OUT parameters use OUT before name and type [OK]

Common Mistakes:

• Using IN instead of OUT
• Swapping parameter order
• Using wrong keywords like RESULT OUT

3. Consider this MySQL procedure call:

CALL calculate_sum(5, 10, @total);
SELECT @total;

Given the procedure signature:

CREATE PROCEDURE calculate_sum(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END;

What will be the output of SELECT @total;?

medium

A. 5

B. Syntax error

C. NULL

D. 15

Solution

1. Step 1: Understand procedure parameters and call

   The procedure takes two input numbers and outputs their sum via the OUT parameter sum. The call passes 5 and 10 as inputs, and @total as the OUT variable.

2. Step 2: Calculate the output value

   The procedure sets sum = 5 + 10 = 15. This value is assigned to the session variable @total.

3. Final Answer:

   15 -> Option D

4. Quick Check:

   5 + 10 = 15 [OK]

Hint: OUT parameters update session variables after CALL [OK]

Common Mistakes:

• Expecting NULL because of no SELECT inside procedure
• Confusing IN and OUT parameters
• Not using session variables for OUT

4. You have this procedure:

CREATE PROCEDURE update_value(INOUT val INT)
BEGIN
  SET val = val + 1;
END;

Which of the following calls will correctly update the session variable @x from 10 to 11?

medium

A. CALL update_value(@x);

B. CALL update_value(10);

C. CALL update_value(INOUT @x);

D. CALL update_value(IN @x);

Solution

1. Step 1: Understand INOUT parameter usage

   INOUT parameters require a variable to pass in and receive updated value. Session variables like @x can be used.

2. Step 2: Analyze each call

   CALL update_value(@x); passes @x correctly. CALL update_value(10); passes a literal 10, which is not a variable. CALL update_value(INOUT @x); uses invalid syntax. CALL update_value(IN @x); uses IN keyword, which is incorrect for INOUT.

3. Final Answer:

   CALL update_value(@x); -> Option A

4. Quick Check:

   INOUT needs variable, not literal [OK]

Hint: Use session variables for INOUT parameters [OK]

Common Mistakes:

• Passing literals instead of variables
• Using IN or INOUT keywords in CALL
• Expecting procedure to update literals

5. You want to create a procedure that swaps two integer values using INOUT parameters a and b. Which of the following procedure bodies correctly swaps the values?

hard

A. BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END

B. BEGIN SET a = b; SET b = a; END

C. BEGIN SET a = a + b; SET b = a - b; SET a = a - b; END

D. BEGIN SET a = b; SET b = a + b; END

Solution

1. Step 1: Understand swapping logic with INOUT parameters

   Swapping requires a temporary variable to hold one value while assigning the other.

2. Step 2: Evaluate each option's correctness

   BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END uses a temporary variable to swap correctly. BEGIN SET a = b; SET b = a; END overwrites a before assigning b. BEGIN SET a = a + b; SET b = a - b; SET a = a - b; END uses arithmetic swap but is valid only if no overflow and no NULLs. BEGIN SET a = b; SET b = a + b; END does not swap correctly.

3. Final Answer:

   BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END -> Option A

4. Quick Check:

   Temporary variable swap = correct [OK]

Hint: Use temp variable to swap INOUT parameters safely [OK]

Common Mistakes:

• Overwriting variables without temp
• Using arithmetic swap without checks
• Ignoring NULL or overflow risks