Parameters (IN, OUT, INOUT) in SQL - Mini Project: Build & Apply

Using Parameters (IN, OUT, INOUT) in SQL Procedures

📖 Scenario: You work at a small bookstore that keeps track of book sales and inventory in a database. You want to create a stored procedure to update the stock of a book after a sale and also return the updated stock count.

🎯 Goal: Create a stored procedure named UpdateStock that uses IN, OUT, and INOUT parameters to update the stock of a book and return the updated stock count.

📋 What You'll Learn

Create a table named Books with columns BookID (integer), Title (string), and Stock (integer).

Insert three books with specific stock values.

Create a stored procedure UpdateStock with parameters:

IN p_BookID INT - the ID of the book to update,

IN p_Sold INT - the number of books sold,

OUT p_NewStock INT - to return the updated stock,

INOUT p_Adjustment INT - an adjustment value to add to stock after sale.

Inside the procedure, update the stock by subtracting p_Sold and adding p_Adjustment.

Set p_NewStock to the updated stock value.

Update p_Adjustment to zero after applying it.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use stored procedures with parameters to update inventory and return results efficiently.

💼 Career

Understanding how to use IN, OUT, and INOUT parameters in SQL procedures is essential for database developers and administrators to write reusable and efficient database code.

1

Create the Books table and insert initial data

Create a table called Books with columns BookID (integer), Title (varchar(100)), and Stock (integer). Then insert these three rows exactly: (1, 'Learn SQL', 50), (2, 'Database Basics', 30), and (3, 'Advanced SQL', 20).

SQL

-- Create the Books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the parameters for the stored procedure

Start creating a stored procedure named UpdateStock with these parameters: IN p_BookID INT, IN p_Sold INT, OUT p_NewStock INT, and INOUT p_Adjustment INT. Just write the procedure header and begin the body with BEGIN and END.

SQL

CREATE PROCEDURE UpdateStock(
    IN p_BookID INT,
    IN p_Sold INT,
    OUT p_NewStock INT,
    INOUT p_Adjustment INT
)
BEGIN
    -- Your code here
END;

Hint

Use CREATE PROCEDURE with the parameter list and BEGIN ... END to start the procedure body.

3

Write the core logic to update stock and set output parameters

Inside the UpdateStock procedure, write SQL to update the Stock in Books by subtracting p_Sold and adding p_Adjustment for the book with BookID = p_BookID. Then select the updated Stock into p_NewStock. Finally, set p_Adjustment to zero.

SQL

CREATE PROCEDURE UpdateStock(
    IN p_BookID INT,
    IN p_Sold INT,
    OUT p_NewStock INT,
    INOUT p_Adjustment INT
)
BEGIN
    -- Update stock by subtracting p_Sold and adding p_Adjustment
    -- Select updated stock into p_NewStock
    -- Set p_Adjustment to zero
    -- Your code here
END;

Hint

Use UPDATE to change stock, SELECT ... INTO to assign output, and SET to reset p_Adjustment.

4

Complete the procedure with proper delimiters

Add the delimiter commands before and after the procedure definition to allow the procedure body to contain semicolons. Use DELIMITER $$ before and DELIMITER ; after the procedure code.

SQL

DELIMITER $$
CREATE PROCEDURE UpdateStock(
    IN p_BookID INT,
    IN p_Sold INT,
    OUT p_NewStock INT,
    INOUT p_Adjustment INT
)
BEGIN
    UPDATE Books
    SET Stock = Stock - p_Sold + p_Adjustment
    WHERE BookID = p_BookID;

    SELECT Stock INTO p_NewStock FROM Books WHERE BookID = p_BookID;

    SET p_Adjustment = 0;
END$$
DELIMITER ;

Hint

Use DELIMITER $$ before and DELIMITER ; after the procedure to allow semicolons inside.

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