Stored Procedure Parameters

Stored procedures in MySQL support three parameter modes: IN, OUT, and INOUT. Understanding these modes is essential for designing flexible and reusable procedures.

Parameter Modes

IN: The default mode. The caller passes a value in; the procedure can read it but changes are not reflected outside.
OUT: The procedure writes a value that the caller can read after the call. The initial value seen inside the procedure is NULL.
INOUT: The caller passes a value in, the procedure can modify it, and the modified value is available to the caller after the call.

Using OUT Parameters

OUT parameters are useful when a procedure needs to return one or more scalar values to the caller without using a result set. You read them using a user-defined variable prefixed with @.

Example

DELIMITER //

CREATE PROCEDURE GetOrderStats(
    IN  cust_id   INT,
    OUT total_orders INT,
    OUT total_spent   DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), SUM(total)
    INTO total_orders, total_spent
    FROM orders
    WHERE customer_id = cust_id;
END //

DELIMITER ;

CALL GetOrderStats(5, @cnt, @spent);
SELECT @cnt AS orders, @spent AS spent;

SQL

Full Editor

Pro Tip

Use OUT parameters when the procedure needs to return scalar results alongside or instead of a result set.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned