MySQL stored procedures reference

In a nutshell

A stored procedure lets you define a reusable “procedure” containing any SQL statement you can then invoke from application code or an SQL console.

Stored procedures support:

• Parameters
• Variables
• Conditional statements like IF or CASE
• Loops such as WHILE, LOOP, and REPEAT
• Invoking other procedures

Recipes

Create procedure without parameters

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
    SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

Create procedure with params

DELIMITER //
CREATE PROCEDURE get_cars_by_year(
    IN year_filter int
)
BEGIN
    SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

Create stored procedure with input and output params

DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
    IN year_filter int,
    OUT cars_number int,
    OUT min_value decimal(10, 2),
    OUT avg_value decimal(10, 2),
    OUT max_value decimal(10, 2)
)
BEGIN
    SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
    INTO cars_number, min_value, avg_value, max_value
    FROM cars
    WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

Execute procedure

CALL get_all_cars()

Remove procedure

DROP PROCEDURE get_car_stats_by_year;