Parameters in Stored Procedures

In SQL, stored procedures can accept parameters to make them dynamic and reusable. These parameters are categorized based on how they interact with the procedure.

Types of Parameters :

  • IN (default): Used to pass input values into the procedure. The procedure can read the value but cannot modify it.
  • OUT: Used to return a value from the procedure to the caller. The procedure sets the value, and the caller retrieves it after execution.
  • INOUT: Used to pass a value in and get a modified value out. The procedure can both read and update the value.

Example: Using IN, OUT, and INOUT Parameters

CREATE PROCEDURE demo_proc(IN a INT, OUT b INT, INOUT c INT)
BEGIN
  SET b = a + 10;     -- OUT: returns a + 10
  SET c = c + a;      -- INOUT: adds a to existing c
END;

Calling the Procedure:

SET @x = 5, @y = 10, @z = 0;
CALL demo_proc(@x, @z, @y);
SELECT @z, @y;

Explanation:

  • @x = 5 is passed as IN (a)
  • @z receives the OUT value (b = 5 + 10 = 15)
  • @y = 10 is passed as INOUT, and becomes 10 + 5 = 15

Final Output

@z = 15   -- OUT parameter
@y = 15   -- INOUT parameter

This structure allows procedures to be flexible and interactive, especially useful in
business logic, reporting, and automation tasks.

PreviousNext