SQL Help

MySQL stored procedures: a practical introduction

Published August 2026 · completing the classic SQL Help series

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_orders but not to DELETE freely.
• Scheduled maintenance — paired with MySQL's EVENT scheduler 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 the original 2006 series would have pitched them as the future, twenty years of hindsight says: a sharp tool for a few jobs, not the default place for your business rules.