SQL Help

MySQL stored procedures: a practical introduction

Updated 26 October 2026

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 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.