by
SQL Tutorial › Performance & Objects › Stored Procedures

SQL Stored Procedures

AdvancedLesson 63 of 6914 min read7 queries run on MySQL 8.4

A stored procedure is a named block of SQL that lives in the database and runs with CALL. It can take parameters, use variables, IF and loops, run several statements in a transaction and return result sets. Teams use procedures to keep important business operations, like placing an order, in one tested place that every application calls the same way.

Advertisement
In this lesson
  • Create and call a procedure with DELIMITER and CALL
  • Pass values in and out with IN and OUT parameters
  • Use variables, IF and loops
  • Handle errors and wrap work in a transaction

Why DELIMITER?

A procedure body contains several statements, each ending in ;. The mysql client would send the CREATE PROCEDURE at the first ;, cutting it off. DELIMITER // temporarily changes the statement terminator so the whole body is sent as one statement:

MySQL
DELIMITER //CREATE PROCEDURE name(...)BEGIN  statement1;  statement2;END //DELIMITER ;

MySQL Workbench and most drivers need the same trick or send the body as one statement automatically.

A first procedure

MySQL
DELIMITER //CREATE PROCEDURE list_department(IN p_dept_id INT)BEGIN  SELECT emp_id, first_name, job_title, salary  FROM employees  WHERE dept_id = p_dept_id  ORDER BY salary DESC;END //DELIMITER ; CALL list_department(2);
ResultMySQL 8.4
mysql> CREATE PROCEDURE list_department(IN p_dept_id INT)
Query OK, 0 rows affected
mysql> CALL list_department(2)
emp_idfirst_namejob_titlesalary
105VikramSales Manager105000
106NehaSales Executive54000
107ArjunSales Executive48000
3 rows in set
How this query works, step by step
  1. IN p_dept_id INT declares an input parameter. A p_ prefix keeps parameter names apart from column names.
  2. The body is a normal SELECT that uses the parameter.
  3. CALL list_department(2) runs it for department 2 and returns its result set.

OUT parameters

An OUT parameter sends a value back to the caller through a session variable:

MySQL
DELIMITER //CREATE PROCEDURE dept_stats(IN p_dept_id INT, OUT p_staff INT, OUT p_payroll INT)BEGIN  SELECT COUNT(*), SUM(salary)  INTO p_staff, p_payroll  FROM employees  WHERE dept_id = p_dept_id;END //DELIMITER ; CALL dept_stats(1, @staff, @payroll);SELECT @staff AS staff, @payroll AS payroll;
ResultMySQL 8.4
mysql> CREATE PROCEDURE dept_stats(IN p_dept_id INT, OUT p_staff INT, OUT p_p…
Query OK, 0 rows affected
mysql> CALL dept_stats(1, @staff, @payroll)
Query OK, 1 row affected
mysql> SELECT @staff AS staff, @payroll AS payroll
staffpayroll
5392000
1 row in set
How this query works, step by step
  1. SELECT ... INTO p_staff, p_payroll stores the query’s single row into the OUT parameters.
  2. The caller passes user variables @staff and @payroll to receive them.
  3. Engineering has 5 people and a payroll of 392,000.

INOUT parameters are both: the caller passes a value in and gets a changed value back.

Variables and IF

MySQL
DELIMITER //CREATE PROCEDURE stock_status(IN p_product_id INT, OUT p_status VARCHAR(20))BEGIN  DECLARE v_stock INT;  SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id;  IF v_stock IS NULL THEN    SET p_status = 'unknown product';  ELSEIF v_stock = 0 THEN    SET p_status = 'out of stock';  ELSEIF v_stock < 20 THEN    SET p_status = 'low';  ELSE    SET p_status = 'ok';  END IF;END //DELIMITER ; CALL stock_status(3, @s1);CALL stock_status(5, @s2);CALL stock_status(1, @s3);CALL stock_status(99, @s4);SELECT @s1, @s2, @s3, @s4;
ResultMySQL 8.4
mysql> CREATE PROCEDURE stock_status(IN p_product_id INT, OUT p_status VARCHA…
Query OK, 0 rows affected
mysql> CALL stock_status(3, @s1)
Query OK, 1 row affected
mysql> CALL stock_status(5, @s2)
Query OK, 1 row affected
mysql> CALL stock_status(1, @s3)
Query OK, 1 row affected
mysql> CALL stock_status(99, @s4)
Query OK, 0 rows affected
mysql> SELECT @s1, @s2, @s3, @s4
@s1@s2@s3@s4
out of stocklowokunknown product
1 row in set
How this query works, step by step
  1. DECLARE v_stock INT creates a local variable (only valid inside the BEGIN ... END block).
  2. SELECT stock INTO v_stock reads the product’s stock; for an unknown id no row is found and the variable stays NULL.
  3. IF ... ELSEIF ... ELSE ... END IF picks the status.

Loops

Create a table of the next 5 invoice numbers with a WHILE loop:

MySQL
CREATE TABLE invoice_numbers (n INT);DELIMITER //CREATE PROCEDURE make_invoices(IN p_start INT, IN p_count INT)BEGIN  DECLARE i INT DEFAULT 0;  WHILE i < p_count DO    INSERT INTO invoice_numbers VALUES (p_start + i);    SET i = i + 1;  END WHILE;END //DELIMITER ; CALL make_invoices(5001, 5);SELECT * FROM invoice_numbers;
ResultMySQL 8.4
mysql> CREATE TABLE invoice_numbers (n INT)
Query OK, 0 rows affected
mysql> CREATE PROCEDURE make_invoices(IN p_start INT, IN p_count INT)
Query OK, 0 rows affected
mysql> CALL make_invoices(5001, 5)
Query OK, 1 row affected
mysql> SELECT * FROM invoice_numbers
n
5001
5002
5003
5004
5005
5 rows in set

MySQL also has REPEAT ... UNTIL ... END REPEAT and LOOP with LEAVE. Remember that one set-based INSERT ... SELECT is usually much faster than a loop.

A business operation with a transaction

Place an order: check stock, insert the order and its line, reduce stock, all or nothing:

MySQL
DELIMITER //CREATE PROCEDURE place_order(IN p_order_id INT, IN p_customer_id INT, IN p_product_id INT, IN p_qty INT)BEGIN  DECLARE v_stock INT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    ROLLBACK;    RESIGNAL;  END;   START TRANSACTION;  SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id FOR UPDATE;  IF v_stock IS NULL OR v_stock < p_qty THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not enough stock';  END IF;  INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (p_order_id, p_customer_id, '2026-01-15', 'Pending');  INSERT INTO order_items (order_id, product_id, quantity) VALUES (p_order_id, p_product_id, p_qty);  UPDATE products SET stock = stock - p_qty WHERE product_id = p_product_id;  COMMIT;END //DELIMITER ; CALL place_order(2001, 6, 1, 2);SELECT order_id, customer_id, status FROM orders WHERE order_id = 2001;SELECT stock FROM products WHERE product_id = 1;
ResultMySQL 8.4
mysql> CREATE PROCEDURE place_order(IN p_order_id INT, IN p_customer_id INT, …
Query OK, 0 rows affected
mysql> CALL place_order(2001, 6, 1, 2)
Query OK, 0 rows affected
mysql> SELECT order_id, customer_id, status FROM orders WHERE order_id = 2001
order_idcustomer_idstatus
20016Pending
1 row in set
mysql> SELECT stock FROM products WHERE product_id = 1
stock
118
1 row in set
How this query works, step by step
  1. DECLARE EXIT HANDLER FOR SQLEXCEPTION runs if any statement fails: it rolls back and re-raises the error (RESIGNAL) so the caller sees it.
  2. SELECT ... FOR UPDATE locks the product row while we check and change it.
  3. SIGNAL SQLSTATE '45000' raises a custom error when there is not enough stock.
  4. Manoj (customer 6) orders 2 mice: the order exists and stock drops from 120 to 118.

Asking for more than is in stock raises the custom error, and nothing is saved:

MySQL
CALL place_order(2002, 6, 3, 1);
ResultMySQL 8.4
ERROR 1644 (45000): Not enough stock
MySQL
SELECT COUNT(*) AS order_2002_rows FROM orders WHERE order_id = 2002;
ResultMySQL 8.4
order_2002_rows
0
1 row in set

Manage procedures

TaskStatement
List proceduresSHOW PROCEDURE STATUS WHERE Db = 'company';
See the codeSHOW CREATE PROCEDURE place_order;
RemoveDROP PROCEDURE IF EXISTS place_order;
Changedrop and create again (there is no ALTER for the body)

Pros and cons

  • Pros: one tested implementation of a business rule; fewer round trips; permissions can be granted on the procedure instead of the tables.
  • Cons: harder to version, test and debug than application code; logic split between two places; MySQL’s procedural language is limited.

Quick quiz

1. Why is DELIMITER needed when creating a procedure in the mysql client?

2. How do you run a stored procedure?

3. How does a procedure return a single value to the caller?

Stored procedure FAQ

What is a stored procedure in MySQL?

A named, stored set of SQL statements with optional parameters and control flow, executed with CALL.

What is the difference between a stored procedure and a function?

A function returns one value and can be used inside SQL expressions. A procedure is run with CALL, can return result sets and OUT parameters, and can manage transactions. See User-Defined Functions.

How do I handle errors in a MySQL procedure?

Declare a handler: DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; and raise your own errors with SIGNAL SQLSTATE '45000'.