MySQL – Stored Procedures and Functions

A stored procedure is code that lives in the database and runs there. MySQL gives you variables, conditionals, loops, cursors and error handlers — a small procedural language wrapped around SQL. This lesson covers the syntax and then the question that matters more: whether logic belongs here at all.

DELIMITER, and why you need it

A procedure body contains semicolons. The client uses a semicolon to decide where a statement ends, so without help it sends the first line of your procedure and reports a syntax error. Change the delimiter first, then change it back:

DELIMITER $$

CREATE PROCEDURE recalc_order_total(IN p_order_id BIGINT, OUT p_total DECIMAL(10,2))
BEGIN
    DECLARE v_goods DECIMAL(10,2) DEFAULT 0.00;
    DECLARE v_fee   DECIMAL(10,2) DEFAULT 0.00;

    SELECT COALESCE(SUM(line_total), 0.00) INTO v_goods FROM order_item      WHERE order_id = p_order_id;
    SELECT delivery_fee                    INTO v_fee   FROM customer_order  WHERE id       = p_order_id;

    SET p_total = v_goods + ROUND(v_goods * 0.0725, 2) + v_fee;

    UPDATE customer_order
       SET subtotal = v_goods, tax = ROUND(v_goods * 0.0725, 2), total = p_total
     WHERE id = p_order_id;
END$$

DELIMITER ;

DELIMITER is a client command, not SQL — it never reaches the server. That is also why it does not appear in scripts run through some drivers, which have their own way of handling this.

Calling it

CALL recalc_order_total(1, @t);

SELECT @t AS recalculated_total;

+--------------------+
| recalculated_total |
+--------------------+
|              28.63 |
+--------------------+

Three parameter modes: IN (the default, passed in), OUT (written back), and INOUT (both). @t is a session variable, which is how an OUT value gets back to you.

DECLARE declares local variables and must come first in the block, before any other statement. SELECT ... INTO var assigns from a query — and note it errors if the query returns more than one row.

Naming, and the shadowing trap

The p_ and v_ prefixes are not decoration. A parameter or variable with the same name as a column shadows the column, silently:

-- unavailable: an excerpt from inside the procedure body above, not a standalone statement.
-- if the parameter were called `order_id`, this would read:
--     WHERE order_id = order_id     -- always true. Every row.
SELECT COALESCE(SUM(line_total), 0.00) INTO v_goods FROM order_item WHERE order_id = p_order_id;

No error, no warning, and an UPDATE written that way changes the whole table. Prefix parameters and locals, always.

Control flow

DELIMITER $$

CREATE PROCEDURE describe_order(IN p_order_id BIGINT, OUT p_note VARCHAR(60))
BEGIN
    DECLARE v_status VARCHAR(30);
    DECLARE v_items  INT DEFAULT 0;

    SELECT status INTO v_status FROM customer_order WHERE id = p_order_id;
    SELECT COUNT(*) INTO v_items FROM order_item WHERE order_id = p_order_id;

    IF v_status = 'CANCELLED' THEN
        SET p_note = 'cancelled';
    ELSEIF v_items = 0 THEN
        SET p_note = 'empty order';
    ELSE
        SET p_note = CONCAT(v_items, ' item(s), ', LOWER(v_status));
    END IF;
END$$

DELIMITER ;

CASE works as a statement too, and there are three loops — WHILE ... DO ... END WHILE, REPEAT ... UNTIL ... END REPEAT, and a bare LOOP you exit with LEAVE.

Stored functions

DELIMITER $$

CREATE FUNCTION order_item_count(p_order_id BIGINT) RETURNS INT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE v INT;
    SELECT COUNT(*) INTO v FROM order_item WHERE order_id = p_order_id;
    RETURN v;
END$$

DELIMITER ;

SELECT id, customer_name, order_item_count(id) AS items FROM customer_order ORDER BY id LIMIT 4;

+----+---------------+-------+
| id | customer_name | items |
+----+---------------+-------+
|  1 | Demo Customer |     2 |
|  2 | Alex Rivera   |     1 |
|  3 | Demo Customer |     2 |
|  4 | Sam Chen      |     1 |
+----+---------------+-------+

A function returns one value and can be used inside a query; a procedure cannot. That convenience has a sharp edge: the function runs once per row, so on a large table that is a query per row. The equivalent LEFT JOIN … GROUP BY does it in one pass. Use functions for cheap scalar work, not for hiding a query.

The trailing characteristics are required. DETERMINISTIC promises the same inputs give the same output; READS SQL DATA says it reads but does not write. Without one of these MySQL refuses to create the function when binary logging is on, because it cannot decide whether the function is safe to replicate.

Error handling

DELIMITER $$

CREATE PROCEDURE safe_add_crust(IN p_name VARCHAR(80), IN p_delta DECIMAL(10,2), OUT p_result VARCHAR(40))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 'failed, rolled back';
    END;

    START TRANSACTION;
    INSERT INTO crust (name, price_delta, active, display_order, public_id, created_at, updated_at, deleted)
    VALUES (p_name, p_delta, TRUE, 99, CONCAT('cccccccc-0000-4000-8000-', LPAD(FLOOR(RAND()*1e9), 12, '0')),
            '2026-01-01', '2026-01-01', FALSE);
    COMMIT;
    SET p_result = 'inserted';
END$$

DELIMITER ;

DECLARE ... HANDLER is MySQL's try/catch. EXIT leaves the block, CONTINUE carries on. You can catch a specific error (FOR 1062), a SQLSTATE class, or the shorthands SQLEXCEPTION, SQLWARNING and NOT FOUND.

NOT FOUND is what stops a cursor loop, and forgetting it is the classic way to write an infinite one:

DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id FROM customer_order WHERE status = 'PAID';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
    FETCH cur INTO v_id;
    IF done = 1 THEN LEAVE read_loop; END IF;
    -- ... work on v_id ...
END LOOP;
CLOSE cur;

And a note on cursors generally: a cursor loop is row-by-row processing, which is what SQL is specifically good at avoiding. If you find yourself writing one, check first whether a single UPDATE ... JOIN would do it.

Should the logic live here?

An honest assessment, because this is a genuine architectural choice and the answer has moved over the years.

The pizza application puts its pricing logic in a Java PricingService, and that is the right default for most teams today: it is testable, reviewable, and it scales horizontally.

Procedures earn their place for bulk data work — a nightly aggregation, a migration over millions of rows, an archival job — where moving the data to the application and back is the expensive part. Use them for that, keep the definitions in migration files so they are version-controlled, and do not put business rules there by default.

Managing them

SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW CREATE PROCEDURE recalc_order_total;
DROP PROCEDURE IF EXISTS recalc_order_total;
DROP FUNCTION  IF EXISTS order_item_count;

There is no CREATE OR REPLACE for routines, so changing one means DROP then CREATE — which is a moment where the procedure does not exist. Note also that mysqldump does not include routines unless you pass --routines; see backup and restore.

What to remember

• DELIMITER is a client command, and you need it to define a body.
• Prefix parameters and locals — a name matching a column shadows it silently.
• A stored function runs once per row. Do not hide a query in one.
• DECLARE ... HANDLER is try/catch; NOT FOUND ends a cursor loop.
• Default to logic in the application; use procedures for bulk work, and keep them in migrations.