Procedure parameters (IN, OUT, INOUT) in MySQL

Introduction

Procedure parameters let you send data into a procedure and get data back from it. They help procedures work with different values each time.

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

You want a procedure to return a value after running.

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

You want to reuse a procedure with different data without changing its code.

You want to organize your database logic into small, manageable parts.

Syntax

MySQL

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

IN parameters send data into the procedure.

OUT parameters send data back from the procedure.

INOUT parameters do both: send data in and send updated data out.

Examples

This procedure takes a name as input and returns a greeting message.

MySQL

CREATE PROCEDURE greet_user(IN username VARCHAR(50))
BEGIN
  SELECT CONCAT('Hello, ', username) AS greeting;
END;

This procedure calculates total sales and sends the result back using an OUT parameter.

MySQL

CREATE PROCEDURE get_total_sales(OUT total DECIMAL(10,2))
BEGIN
  SELECT SUM(amount) INTO total FROM sales;
END;

This procedure updates the balance by adding a change amount and returns the new balance.

MySQL

CREATE PROCEDURE adjust_balance(INOUT balance DECIMAL(10,2), IN change DECIMAL(10,2))
BEGIN
  SET balance = balance + change;
END;

Sample Program

This procedure takes a current balance and a deposit amount. It adds the deposit to the balance and returns the updated balance both as an INOUT and an OUT parameter.

MySQL

DELIMITER $$
CREATE PROCEDURE update_and_get_balance(
  INOUT current_balance DECIMAL(10,2),
  IN deposit DECIMAL(10,2),
  OUT new_balance DECIMAL(10,2)
)
BEGIN
  SET current_balance = current_balance + deposit;
  SET new_balance = current_balance;
END$$
DELIMITER ;

-- Declare variables to test the procedure
SET @balance = 100.00;
SET @deposit = 50.00;
CALL update_and_get_balance(@balance, @deposit, @new_balance);
SELECT @balance AS updated_balance, @new_balance AS output_balance;

Important Notes

Use IN when you only need to send data into the procedure.

Use OUT when you want the procedure to send data back to you.

Use INOUT when you want to send data in and get the changed data back.

Summary

Procedure parameters control how data moves in and out of procedures.

IN is for input, OUT is for output, and INOUT is for both.

They help make procedures flexible and reusable with different data.