Parameters (IN, OUT, INOUT) in SQL

Introduction

Parameters let you send and receive values when running a stored procedure. They help you share information between your code and the database.

You want to give a procedure some input values to work with.

You want a procedure to send back results or status after running.

You want a procedure to both receive input and send back output.

You want to reuse a procedure with different inputs without rewriting it.

You want to keep your database logic organized and clear.

Syntax

SQL

CREATE PROCEDURE procedure_name (
  IN param1 datatype,
  OUT param2 datatype,
  INOUT param3 datatype
)
BEGIN
  -- procedure body
END;

IN means the parameter is for input only.

OUT means the parameter is for output only.

INOUT means the parameter can be used for both input and output.

Examples

This procedure takes two input numbers and returns their sum as output.

SQL

CREATE PROCEDURE AddNumbers(
  IN num1 INT,
  IN num2 INT,
  OUT sum INT
)
BEGIN
  SET sum = num1 + num2;
END;

This procedure takes a number, adds 10 to it, and returns the new value using the same parameter.

SQL

CREATE PROCEDURE UpdateAndReturn(
  INOUT value INT
)
BEGIN
  SET value = value + 10;
END;

Sample Program

This procedure calculates the area of a rectangle using width and height as input, and returns the area as output.

We call it with width=5 and height=4, then select the output variable to see the result.

SQL

DELIMITER $$
CREATE PROCEDURE CalculateArea(
  IN width INT,
  IN height INT,
  OUT area INT
)
BEGIN
  SET area = width * height;
END$$
DELIMITER ;

-- Call the procedure
CALL CalculateArea(5, 4, @result);
SELECT @result AS Area;

Important Notes

You must use session variables (like @result) to get OUT or INOUT values after calling a procedure.

IN parameters cannot be changed inside the procedure.

INOUT parameters start with a value and can be changed inside the procedure.

Summary

Parameters let procedures receive inputs and send outputs.

IN is input only, OUT is output only, INOUT is both.

Use session variables to capture OUT and INOUT results after calling procedures.