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

Need a 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;

Need a 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;

Need a 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 ;

Need a hint?

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