SQL stored procedures

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

Example 1:

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 ;

Example 2:

DELIMITER //  
  
CREATE PROCEDURE add_admin_user(IN company_id INT, IN user_id INT)  
BEGIN  
    DECLARE company_user_id INT;  
  
    -- Add company user  
    INSERT INTO company_user(company_id, user_id, notify)  
    VALUES (company_id, user_id, 1);  
  
    -- Get the ID of the company user just added  
    SELECT LAST_INSERT_ID() INTO company_user_id;  
  
    -- Make user an admin user  
    INSERT INTO company_user_role(company_user_id, role_id)  
    VALUES (company_user_id, 2);  
  
    INSERT INTO company_user_role_selected(company_user_id, role_id)  
    VALUES (company_user_id, 2);  
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;