Procedure parameters (IN, OUT, INOUT) in MySQL - Mini Project: Build & Apply

Using Procedure Parameters (IN, OUT, INOUT) in MySQL

📖 Scenario: You work at a small bookstore. You want to create a stored procedure to manage book stock. The procedure will take the book's ID and the number of new books to add or remove. It will also return the updated stock count.

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

📋 What You'll Learn

Create a table named books with columns book_id (INT) and stock (INT).

Insert one book with book_id 101 and stock 50.

Create a stored procedure update_stock with three parameters: IN p_book_id INT, INOUT p_change INT, and OUT p_new_stock INT.

The procedure should update the stock by adding p_change to the current stock of the book with p_book_id.

The procedure should set p_new_stock to the updated stock value.

If the updated stock is less than zero, set stock to zero and p_change to the actual change applied.

💡 Why This Matters

🌍 Real World

Bookstores and inventory systems often need to update stock counts safely and return updated values to applications.

💼 Career

Knowing how to use procedure parameters is essential for database developers and administrators to write reusable and efficient database logic.

1

Create the books table and insert initial data

Create a table called books with columns book_id (INT) and stock (INT). Insert one row with book_id 101 and stock 50.

MySQL

-- Create the books table and insert one book
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the book.

2

Declare the stored procedure with parameters

Create a stored procedure named update_stock with three parameters: IN p_book_id INT, INOUT p_change INT, and OUT p_new_stock INT.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  stock INT
);

INSERT INTO books (book_id, stock) VALUES (101, 50);

-- Create the procedure header with parameters
-- Your code here

Need a hint?

Use CREATE PROCEDURE with the correct parameter modes and names.

3

Write the core logic to update stock and set output parameters

Inside the update_stock procedure, write SQL to update the stock of the book with book_id = p_book_id by adding p_change. Then set p_new_stock to the updated stock. If the updated stock is less than zero, set stock to zero and adjust p_change to the actual change applied.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  stock INT
);

INSERT INTO books (book_id, stock) VALUES (101, 50);

DELIMITER $$
CREATE PROCEDURE update_stock(
  IN p_book_id INT,
  INOUT p_change INT,
  OUT p_new_stock INT
)
BEGIN
  -- Update stock and set output parameters here
  -- Your code here
END$$
DELIMITER ;

Need a hint?

Use DECLARE to create a variable, SELECT INTO to get current stock, and IF to check for negative stock.

4

Complete the procedure with delimiters and test setup

Ensure the procedure ends with END$$ and the delimiter is reset to ;. This completes the procedure definition.

MySQL

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  stock INT
);

INSERT INTO books (book_id, stock) VALUES (101, 50);

DELIMITER $$
CREATE PROCEDURE update_stock(
  IN p_book_id INT,
  INOUT p_change INT,
  OUT p_new_stock INT
)
BEGIN
  DECLARE current_stock INT;
  SELECT stock INTO current_stock FROM books WHERE book_id = p_book_id;
  SET current_stock = current_stock + p_change;
  IF current_stock < 0 THEN
    SET p_change = - (SELECT stock FROM books WHERE book_id = p_book_id);
    SET current_stock = 0;
  END IF;
  UPDATE books SET stock = current_stock WHERE book_id = p_book_id;
  SET p_new_stock = current_stock;
END$$
DELIMITER ;

-- Procedure complete

Need a hint?

Remember to reset the delimiter to ; after the procedure.