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

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

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 ;

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

Hint

Remember to reset the delimiter to ; after the procedure.

Practice

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

easy

A. It declares a variable inside the procedure without passing values.

B. It passes a value into the procedure but does not allow changes to be sent back.

C. It allows both passing a value in and sending a changed value back.

D. It allows the procedure to send a value back to the caller only.

Solution

1. Step 1: Understand the role of IN parameters

   IN parameters are used to pass values into a procedure. They act like input-only variables.

2. Step 2: Clarify output behavior of IN parameters

   Changes to IN parameters inside the procedure do not affect the caller's variables; they are input-only.

3. Final Answer:

   It passes a value into the procedure but does not allow changes to be sent back. -> Option B

4. Quick Check:

   IN = input only [OK]

Hint: IN means input only, no output changes sent back [OK]

Common Mistakes:

• Thinking IN parameters can send data back
• Confusing IN with OUT or INOUT
• Assuming IN parameters can be modified outside

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

easy

A. CREATE PROCEDURE myProc(param OUT INT) BEGIN END;

B. CREATE PROCEDURE myProc(INOUT param INT) BEGIN END;

C. CREATE PROCEDURE myProc(OUT param INT) BEGIN END;

D. CREATE PROCEDURE myProc(param INT OUT) BEGIN END;

Solution

1. Step 1: Recall correct parameter declaration order

   In MySQL, the parameter mode (IN, OUT, INOUT) comes before the parameter name and type.

2. Step 2: Check each option's syntax

   CREATE PROCEDURE myProc(OUT param INT) BEGIN END; uses 'OUT param INT' which is correct. Placing OUT after the parameter name or type is invalid syntax.

3. Final Answer:

   CREATE PROCEDURE myProc(OUT param INT) BEGIN END; -> Option C

4. Quick Check:

   Parameter mode before name and type [OK]

Hint: Parameter mode (IN/OUT/INOUT) always comes first [OK]

Common Mistakes:

• Placing OUT after parameter name or type
• Mixing parameter mode with data type
• Omitting BEGIN and END block

3. Consider this procedure call and definition:

CREATE PROCEDURE addValues(IN a INT, INOUT b INT, OUT c INT)
BEGIN
  SET b = b + a;
  SET c = a + b;
END;

CALL addValues(5, @varB, @varC);

If @varB is initially 10, what will be the values of @varB and @varC after the call?

medium

A. @varB = 15, @varC = 20

B. @varB = 10, @varC = 15

C. @varB = 5, @varC = 10

D. @varB = 15, @varC = 10

Solution

1. Step 1: Understand parameter roles and initial values

   a = 5 (IN), b = 10 (INOUT), c is OUT (initially NULL). INOUT means b can be read and updated.

2. Step 2: Calculate new values inside procedure

   SET b = b + a = 10 + 5 = 15; SET c = a + b = 5 + 15 = 20.

3. Final Answer:

   @varB = 15, @varC = 20 -> Option A

4. Quick Check:

   INOUT b updated, OUT c set correctly [OK]

Hint: INOUT changes persist; OUT must be set inside procedure [OK]

Common Mistakes:

• Assuming INOUT parameter does not change
• Confusing OUT parameter initial value
• Ignoring that OUT must be assigned inside procedure

4. Given this procedure:

CREATE PROCEDURE testProc(INOUT x INT)
BEGIN
  SET x = x + 1;
END;

Which of the following calls will cause an error?

medium

A. CALL testProc(@varX); -- @varX is a session variable

B. CALL testProc(NULL);

C. SET @varX = 10; CALL testProc(@varX);

D. CALL testProc(5);

Solution

1. Step 1: Understand INOUT parameter requirements

   INOUT parameters require a variable to pass by reference, so the procedure can read and modify it.

2. Step 2: Analyze each call

   CALL testProc(5); passes a literal (5), which is not a variable, causing an error. The other options pass variables or NULL variables, which are allowed.

3. Final Answer:

   CALL testProc(5); -> Option D

4. Quick Check:

   INOUT needs variable, not literal [OK]

Hint: INOUT requires variable, not constant literal [OK]

Common Mistakes:

• Passing literals to INOUT parameters
• Confusing INOUT with IN parameters
• Assuming NULL literal is invalid for INOUT

5. You want to create a procedure that takes two numbers, adds them, and returns the sum and the product. Which parameter types should you use for the inputs and outputs?

hard

A. IN for both inputs, OUT for sum and product outputs

B. INOUT for inputs, IN for outputs

C. OUT for inputs, IN for outputs

D. IN for inputs, INOUT for outputs

Solution

1. Step 1: Identify input and output roles

   The two numbers are inputs only, so they should be IN parameters. The sum and product are results to send back, so they should be OUT parameters.

2. Step 2: Match parameter types to roles

   IN parameters accept input values; OUT parameters return output values. INOUT is for parameters used as both input and output, which is not needed here.

3. Final Answer:

   IN for both inputs, OUT for sum and product outputs -> Option A

4. Quick Check:

   Inputs = IN, Outputs = OUT [OK]

Hint: Use IN for inputs, OUT for outputs to separate roles clearly [OK]

Common Mistakes:

• Using INOUT unnecessarily for outputs
• Confusing OUT as input
• Using IN for output parameters