Skip to content
elephantoo

Stored procedures

Lesson 24 of 31 18 min read

DELIMITER, IN/OUT parameters, variables, IF/CASE, loops, cursors and error handlers.

A stored procedure is a named block of SQL statements saved inside the database, which you run with CALL. Procedures can take parameters, use variables, branch, loop and handle errors. Teams use them to bundle multi-step operations into one call, enforce business rules close to the data and reduce network round trips.

Your first procedure#

SQL
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    owner VARCHAR(20) NOT NULL,
    balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
);
CREATE TABLE transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_id INT NOT NULL, to_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts VALUES (1, 'Asha', 1000.00), (2, 'Rohan', 200.00), (3, 'Meera', 50.00);
SQL
DELIMITER $$

CREATE PROCEDURE list_rich_accounts(IN min_balance DECIMAL(10,2))
BEGIN
    SELECT id, owner, balance
    FROM accounts
    WHERE balance >= min_balance
    ORDER BY balance DESC;
END $$

DELIMITER ;

CALL list_rich_accounts(100);
Output
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Asha  | 1000.00 |
|  2 | Rohan |  200.00 |
+----+-------+---------+

Why DELIMITER? The procedure body contains semicolons. Normally the mysql client sends a statement to the server as soon as it sees ;, which would cut the CREATE PROCEDURE in half. DELIMITER $$ temporarily makes $$ the end-of-statement marker, and DELIMITER ; switches back. DELIMITER is a client command: GUI tools and application drivers send the whole statement at once and don't need it.

Parameters: IN, OUT and INOUT#

  • IN (the default): a value passed in.
  • OUT: the procedure sets it and the caller reads it afterwards.
  • INOUT: both.
SQL
DELIMITER $$
CREATE PROCEDURE account_stats(OUT total DECIMAL(12,2), OUT how_many INT)
BEGIN
    SELECT SUM(balance), COUNT(*) INTO total, how_many FROM accounts;
END $$
DELIMITER ;

CALL account_stats(@t, @n);
SELECT @t AS total, @n AS accounts;
Output
+---------+----------+
| total   | accounts |
+---------+----------+
| 1250.00 |        3 |
+---------+----------+

@t and @n are user variables: session-wide and untyped, written with an @. SELECT ... INTO stores a query's single-row result into variables.

Local variables, IF and CASE#

Inside BEGIN ... END, declare local variables with DECLARE (at the top of the block) and assign them with SET or SELECT ... INTO:

SQL
DELIMITER $$
CREATE PROCEDURE classify_account(IN acc_id INT, OUT label VARCHAR(20))
BEGIN
    DECLARE bal DECIMAL(10,2);

    SELECT balance INTO bal FROM accounts WHERE id = acc_id;

    IF bal IS NULL THEN
        SET label = 'not found';
    ELSEIF bal >= 500 THEN
        SET label = 'premium';
    ELSEIF bal >= 100 THEN
        SET label = 'standard';
    ELSE
        SET label = 'basic';
    END IF;
END $$
DELIMITER ;

CALL classify_account(1, @a); CALL classify_account(3, @b); CALL classify_account(99, @c);
SELECT @a, @b, @c;
Output
+---------+-------+-----------+
| @a      | @b    | @c        |
+---------+-------+-----------+
| premium | basic | not found |
+---------+-------+-----------+

(When SELECT ... INTO finds no row, the variable keeps its value, which is NULL here, and MySQL issues a "No data" warning.) Stored programs also have a CASE ... WHEN ... THEN ... END CASE statement for multi-way branching.

Loops#

MySQL has three loop forms: WHILE ... DO ... END WHILE, REPEAT ... UNTIL ... END REPEAT and labelled LOOP ... END LOOP (exited with LEAVE label, or skipped ahead with ITERATE label):

SQL
DELIMITER $$
CREATE PROCEDURE make_calendar(IN start_date DATE, IN days INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DROP TEMPORARY TABLE IF EXISTS calendar;
    CREATE TEMPORARY TABLE calendar (d DATE PRIMARY KEY);
    WHILE i < days DO
        INSERT INTO calendar VALUES (start_date + INTERVAL i DAY);
        SET i = i + 1;
    END WHILE;
    SELECT MIN(d) AS first_day, MAX(d) AS last_day, COUNT(*) AS days FROM calendar;
END $$
DELIMITER ;

CALL make_calendar('2026-10-01', 31);
Output
+------------+------------+------+
| first_day  | last_day   | days |
+------------+------------+------+
| 2026-10-01 | 2026-10-31 |   31 |
+------------+------------+------+

Remember that SQL is set-based. A single INSERT ... SELECT (or a recursive CTE) is almost always faster than a loop that inserts one row at a time. Use loops when the steps really are procedural.

Transactions and error handling#

Now a realistic procedure: an atomic money transfer that validates its input, uses a transaction, and rolls back on any error.

SQL
DELIMITER $$
CREATE PROCEDURE transfer(IN p_from INT, IN p_to INT, IN p_amount DECIMAL(10,2))
BEGIN
    DECLARE v_found INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;            -- pass the original error to the caller
    END;

    IF p_amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be positive';
    END IF;

    START TRANSACTION;
        -- lock both rows (InnoDB locks them in index order, avoiding deadlocks)
        SELECT COUNT(*) INTO v_found FROM accounts WHERE id IN (p_from, p_to) FOR UPDATE;
        IF v_found < 2 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown account';
        END IF;

        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
        INSERT INTO transfers (from_id, to_id, amount) VALUES (p_from, p_to, p_amount);
    COMMIT;
END $$
DELIMITER ;
SQL
CALL transfer(1, 2, 300);
CALL transfer(3, 1, 75);      -- Meera only has 50: CHECK constraint fails
CALL transfer(1, 2, -5);
CALL transfer(1, 42, 10);     -- no account 42
SELECT id, owner, balance FROM accounts;
SELECT from_id, to_id, amount FROM transfers;
Output
ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.
ERROR 1644 (45000): Amount must be positive
ERROR 1644 (45000): Unknown account
+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Asha  |  700.00 |
|  2 | Rohan |  500.00 |
|  3 | Meera |   50.00 |
+----+-------+---------+
+---------+-------+--------+
| from_id | to_id | amount |
+---------+-------+--------+
|       1 |     2 | 300.00 |
+---------+-------+--------+

Only the valid transfer went through. The failing ones were rolled back completely, and the caller still received each error. Prefixing parameters with p_ (and local variables with v_) is a good habit: a parameter named like a column (amount, id) silently shadows that column inside queries, and some names clash with SQL syntax. For example, a variable called names fails because SET names = ... looks like the SET NAMES statement.

  • DECLARE ... HANDLER FOR condition catches errors. Conditions can be SQLEXCEPTION, SQLWARNING, NOT FOUND, a specific error number (FOR 1062) or an SQLSTATE.
  • An EXIT handler leaves the block after running. A CONTINUE handler carries on with the next statement.
  • SIGNAL SQLSTATE '45000' raises your own error ('45000' means "unhandled user-defined exception"). RESIGNAL re-raises the current one. GET DIAGNOSTICS CONDITION 1 @msg = MESSAGE_TEXT; reads the error details inside a handler.

Cursors: row-by-row processing#

A cursor walks through a query's result one row at a time. A NOT FOUND handler tells you when the rows run out:

SQL
DELIMITER $$
CREATE PROCEDURE owner_list(OUT p_names VARCHAR(500))
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE v_owner VARCHAR(20);
    DECLARE cur CURSOR FOR SELECT owner FROM accounts ORDER BY owner;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET p_names = '';
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_owner;
        IF done THEN LEAVE read_loop; END IF;
        SET p_names = CONCAT_WS(', ', NULLIF(p_names, ''), v_owner);
    END LOOP;
    CLOSE cur;
END $$
DELIMITER ;

CALL owner_list(@names);
SELECT @names;
Output
+--------------------+
| @names             |
+--------------------+
| Asha, Meera, Rohan |
+--------------------+

Declarations must come in this order: variables, then cursors, then handlers. (In real code, GROUP_CONCAT does this example in one line. Reach for cursors only when each row needs procedural work.)

Managing procedures#

SQL
SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW CREATE PROCEDURE transfer\G
DROP PROCEDURE IF EXISTS list_rich_accounts;

There's no ALTER for a procedure's body. Drop it and create it again (or use CREATE PROCEDURE IF NOT EXISTS in 8.0.29+). Keep the source in version control like any other code. Users need the EXECUTE privilege to call a procedure. By default it runs with its definer's rights (SQL SECURITY DEFINER), so you can let an app account call transfer without giving it direct UPDATE rights on accounts.

Pros and cons#

👍 Good for👎 Watch out for
Multi-step operations in one round tripLogic split between app and database
Enforcing rules for every clientHarder to unit-test, debug and version
Restricting access via EXECUTEMySQL's procedural language is limited
Batch maintenance jobsScaling CPU-heavy logic on the database server

Many teams keep business logic in the application and use procedures for data-heavy batch jobs and permission boundaries. Choose deliberately and be consistent.

What's next#

Procedures are called. Next you'll write stored functions, which you can use inside SQL expressions just like built-in functions.

Check your understanding

Quick quiz

0/3 answered

  1. 1.Why do we change the DELIMITER before CREATE PROCEDURE in the mysql client?

  2. 2.How do you call a procedure with an OUT parameter and read the result?

  3. 3.What does DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; do?

Finished reading?

Mark this lesson complete to track your progress.