MySQL stored procedures: a practical introduction
A stored procedure is a named block of SQL, with variables, conditions and loops, saved inside the database and run with a single
CALL. A stored function is the same idea but returns a value you can
use inside a query. They've been in MySQL since 5.0 (2005: right when the original
series was being written), and opinions about them run hot. Here's how to write
one, and a level-headed take on when you should.
A first procedure
The one syntactic oddity: the mysql client normally treats
; as "run this now", so you temporarily change the delimiter to get a
whole body in:
DELIMITER //
CREATE PROCEDURE archive_old_orders(IN cutoff DATE, OUT moved INT)
BEGIN
START TRANSACTION;
INSERT INTO orders_archive
SELECT * FROM orders WHERE order_date < cutoff;
SET moved = ROW_COUNT();
DELETE FROM orders WHERE order_date < cutoff;
COMMIT;
END //
DELIMITER ;
CALL archive_old_orders('2025-01-01', @n);
SELECT @n AS orders_archived;
The pieces: IN parameters are inputs; OUT parameters
hand values back (you pass a session variable to receive them);
ROW_COUNT() reports rows touched by the last statement; and wrapping
the move in a transaction means the copy
and the delete succeed or fail together.
Variables, IF and loops
DELIMITER //
CREATE PROCEDURE grade_customer(IN cust_id INT)
BEGIN
DECLARE total DECIMAL(10,2) DEFAULT 0;
DECLARE tier VARCHAR(10);
SELECT COALESCE(SUM(amount), 0) INTO total
FROM orders WHERE customer_id = cust_id;
IF total >= 10000 THEN SET tier = 'gold';
ELSEIF total >= 1000 THEN SET tier = 'silver';
ELSE SET tier = 'bronze';
END IF;
UPDATE customers SET loyalty_tier = tier WHERE customer_id = cust_id;
END //
DELIMITER ;
DECLAREs go first in the block; SELECT ... INTO
captures a single-row result into variables. Loops exist
(WHILE, REPEAT, LOOP, and cursors for
row-by-row processing), but if you find yourself writing a cursor loop, stop and
ask whether a single set-based UPDATE ... JOIN does the job. It
almost always does, and it's almost always ten times faster.
Error handling
By default an error inside a procedure aborts it and propagates. To roll back cleanly and surface a message:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL; -- re-throw so the caller sees the original error
END;
Place that among the DECLAREs at the top of the block. To raise
your own error: SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cutoff must be in
the past';
Functions: use inside queries
DELIMITER // CREATE FUNCTION uk_vat(net DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC RETURN ROUND(net * 1.20, 2); // DELIMITER ; SELECT item, price, uk_vat(price) AS gross FROM products;
Functions must be declared DETERMINISTIC or
NOT DETERMINISTIC (and, with binary logging on, may need
NO SQL / READS SQL DATA): the error message when you
forget is famously baffling, so now you know.
Housekeeping
SHOW PROCEDURE STATUS WHERE Db = 'shop'; SHOW CREATE PROCEDURE archive_old_orders\G DROP PROCEDURE IF EXISTS archive_old_orders;
Note that mysqldump skips routines unless you pass
--routines: a classic way to lose them in a migration. Keep the
source in version control alongside your schema.
So: should you use them?
The honest 2026 answer: sparingly, for specific jobs. Most application logic belongs in the application, where it has tests, version control, code review and a debugger. Stored routines earn their place when:
- The work is data-heavy and round-trip-sensitive: a multi-step batch job that would otherwise shuttle thousands of rows to the app and back. Doing it next to the data is dramatically faster.
- Several applications share one database and must apply the same rule identically (the archive routine above is a good example).
- You want to grant a narrow capability: a user allowed to
CALL archive_old_ordersbut not toDELETEfreely. - Scheduled maintenance: paired with MySQL's
EVENTscheduler for nightly cleanups without a cron job on some server.
What they're bad at: anything you'll iterate on often (deploying a changed procedure to a busy production database is fiddlier than deploying app code), and logic that needs unit tests. Where stored procedures were once pitched as the future, the industry's later experience says: a sharp tool for a few jobs, not the default place for your business rules.