Stored Procedures and Functions

Chapter 8 — Stored Procedures and Functions

Stored procedures and functions let you save a block of SQL — with variables, flow control, and error handling — directly in the database under a name. Any authorised client can call them without knowing the SQL inside. They are the closest thing MySQL has to general-purpose programming.

Called with CALL name(…)
Can return zero, one, or many result sets
Can have IN, OUT, and INOUT parameters
Cannot be used inside a SELECT or expression
Can execute DDL (CREATE TABLE, ALTER …)
Best for: multi-step workflows, batch jobs, encapsulating business logic

Called like a built-in function: SELECT name(…)
Returns exactly one scalar value via RETURN
Only IN parameters (no OUT/INOUT)
Can be used inside SELECT, WHERE, ORDER BY
Cannot execute DDL or use CALL
Best for: reusable calculations, custom formatting, derived values

1. The DELIMITER Problem

MySQL uses ; to end statements. Inside a procedure body you also use ; to end each sub-statement — which would confuse the client into thinking the procedure definition ended too early. The fix: temporarily change the client's statement delimiter to something that won't appear in your code, define the procedure, then restore ;.

mysql> DELIMITER $$          -- change delimiter to $$

mysql> CREATE PROCEDURE example()
       BEGIN
           SELECT 'hello';   -- semicolons inside are fine now
           SELECT 'world';
       END$$              -- $$ ends the CREATE PROCEDURE

mysql> DELIMITER ;          -- restore normal semicolons

DELIMITER is a client command, not SQL. It only works in the mysql CLI and MySQL Workbench. Application code (PHP, Python, Node …) sends one statement at a time, so the DELIMITER trick is never needed there — you pass the entire CREATE PROCEDURE body as a single string to the driver.

2. CREATE PROCEDURE

Simplest possible procedure

DELIMITER $$

CREATE PROCEDURE sp_all_books()
BEGIN
    SELECT book_id, title, price
    FROM   books
    ORDER BY title;
END$$

DELIMITER ;

-- Call it
CALL sp_all_books();

Parameter modes

IN (default)

Caller passes a value in. The procedure can read it but any changes are local — the caller's variable is unchanged.

OUT

Procedure writes a value back to the caller's variable. The initial value is NULL inside the procedure regardless of what the caller passed.

INOUT

Both directions: caller passes a value in AND the procedure can write a new value back. Use when the procedure modifies the caller's value in place.

IN parameter — filter books by genre

DELIMITER $$

CREATE PROCEDURE sp_books_by_genre(IN p_genre_name VARCHAR(80))
BEGIN
    SELECT
        b.title,
        b.price,
        b.stock_quantity
    FROM      books       b
    JOIN      book_genres bg ON bg.book_id  = b.book_id
    JOIN      genres      g  ON g.genre_id  = bg.genre_id
    WHERE     g.name = p_genre_name
    ORDER BY  b.title;
END$$

DELIMITER ;

CALL sp_books_by_genre('Science Fiction');

OUT parameter — return a computed value

DELIMITER $$

CREATE PROCEDURE sp_customer_total(
    IN  p_customer_id  INT,
    OUT p_order_count  INT,
    OUT p_total_spent  DECIMAL(10,2)
)
BEGIN
    SELECT
        COUNT(DISTINCT o.order_id),
        SUM(oi.quantity * oi.unit_price)
    INTO
        p_order_count,
        p_total_spent
    FROM      orders      o
    JOIN      order_items oi ON oi.order_id = o.order_id
    WHERE     o.customer_id = p_customer_id;
END$$

DELIMITER ;

-- Call: pass user variables to receive OUT values
CALL sp_customer_total(7, @orders, @spent);
SELECT @orders AS order_count, @spent AS total_spent;

INOUT parameter — apply a discount and return the new price

DELIMITER $$

CREATE PROCEDURE sp_apply_discount(
    INOUT p_price       DECIMAL(10,2),
    IN    p_discount_pct DECIMAL(5,2)
)
BEGIN
    SET p_price = p_price * (1 - p_discount_pct / 100);
END$$

DELIMITER ;

SET @price = 29.99;
CALL sp_apply_discount(@price, 15);      -- 15% off
SELECT @price;                             -- 25.49

3. Flow Control

DECLARE — local variables

Local variables must be declared at the top of the BEGIN…END block, before any statements. They are scoped to the block and initialised to NULL.

DECLARE v_count     INT             DEFAULT 0;
DECLARE v_name      VARCHAR(100);
DECLARE v_total     DECIMAL(10,2)   DEFAULT 0.00;
DECLARE v_done      BOOLEAN         DEFAULT FALSE;

IF / ELSEIF / ELSE

DELIMITER $$

CREATE PROCEDURE sp_stock_status(IN p_book_id INT)
BEGIN
    DECLARE v_qty INT;

    SELECT stock_quantity INTO v_qty
    FROM   books
    WHERE  book_id = p_book_id;

    IF v_qty IS NULL THEN
        SELECT 'Book not found' AS status;
    ELSEIF v_qty = 0 THEN
        SELECT 'Out of stock'  AS status;
    ELSEIF v_qty < 5 THEN
        SELECT 'Low stock'     AS status, v_qty AS remaining;
    ELSE
        SELECT 'In stock'      AS status, v_qty AS remaining;
    END IF;
END$$

DELIMITER ;

CASE statement

CASE v_segment
    WHEN 'VIP'     THEN SET v_discount = 20;
    WHEN 'Regular' THEN SET v_discount = 10;
    ELSE               SET v_discount = 0;
END CASE;

WHILE loop

DECLARE v_i INT DEFAULT 1;

WHILE v_i <= 10 DO
    INSERT INTO log_table (msg) VALUES (CONCAT('iteration ', v_i));
    SET v_i = v_i + 1;
END WHILE;

REPEAT … UNTIL (do-while equivalent)

REPEAT
    SET v_i = v_i + 1;
UNTIL v_i > 10
END REPEAT;

LOOP with LEAVE (break)

my_loop: LOOP
    SET v_i = v_i + 1;
    IF v_i > 10 THEN
        LEAVE my_loop;    -- break out of the loop
    END IF;
    IF v_i = 5 THEN
        ITERATE my_loop;  -- continue (skip rest of body)
    END IF;
    INSERT INTO log_table (msg) VALUES (v_i);
END LOOP my_loop;

4. A Realistic Procedure — Place an Order

This procedure handles the full order-placement workflow: validate stock, insert the order header, insert line items, decrement stock, and return the new order ID — all in one atomic transaction.

DELIMITER $$

CREATE PROCEDURE sp_place_order(
    IN  p_customer_id  INT,
    IN  p_book_id      INT,
    IN  p_quantity     INT,
    OUT p_order_id     INT,
    OUT p_error_msg    VARCHAR(255)
)
BEGIN
    DECLARE v_stock   INT;
    DECLARE v_price   DECIMAL(10,2);

    -- Default outputs
    SET p_order_id  = NULL;
    SET p_error_msg = NULL;

    -- 1. Check the book exists and has stock
    SELECT stock_quantity, price
    INTO   v_stock, v_price
    FROM   books
    WHERE  book_id = p_book_id;

    IF v_stock IS NULL THEN
        SET p_error_msg = 'Book not found';
        LEAVE sp_place_order;       -- MySQL allows LEAVE on the proc label
    END IF;

    IF v_stock < p_quantity THEN
        SET p_error_msg = CONCAT('Only ', v_stock, ' copies in stock');
        LEAVE sp_place_order;
    END IF;

    -- 2. Everything valid — run inside a transaction
    START TRANSACTION;

        INSERT INTO orders (customer_id, order_date, status)
        VALUES (p_customer_id, NOW(), 'pending');

        SET p_order_id = LAST_INSERT_ID();

        INSERT INTO order_items (order_id, book_id, quantity, unit_price)
        VALUES (p_order_id, p_book_id, p_quantity, v_price);

        UPDATE books
        SET    stock_quantity = stock_quantity - p_quantity
        WHERE  book_id = p_book_id;

    COMMIT;

END$$

DELIMITER ;

-- Usage
CALL sp_place_order(3, 12, 2, @new_order_id, @err);
SELECT @new_order_id, @err;

Always pair START TRANSACTION with error handling in real code. The example above is simplified — a production procedure should declare an EXIT HANDLER FOR SQLEXCEPTION that rolls back the transaction on any error. See the error handling section below.

5. Error Handling — DECLARE … HANDLER

MySQL stored procedures have a structured error-handling mechanism. You declare a handler that fires when a specific condition occurs.

DECLARE {EXIT | CONTINUE} HANDLER FOR condition_value
    handler_statement;

EXIT HANDLER — runs the statement then exits the current BEGIN…END block.
CONTINUE HANDLER — runs the statement then continues from the line after the error.

Transaction rollback on any error

DELIMITER $$

CREATE PROCEDURE sp_safe_transfer(
    IN p_from_book INT,
    IN p_to_book   INT,
    IN p_units     INT
)
BEGIN
    DECLARE v_error BOOLEAN DEFAULT FALSE;

    -- Fire on any SQL error or warning-escalated-to-error
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET v_error = TRUE;
        SELECT 'Transfer failed — rolled back' AS result;
    END;

    START TRANSACTION;

        UPDATE books SET stock_quantity = stock_quantity - p_units
        WHERE  book_id = p_from_book;

        UPDATE books SET stock_quantity = stock_quantity + p_units
        WHERE  book_id = p_to_book;

    COMMIT;

    SELECT 'Transfer complete' AS result;
END$$

DELIMITER ;

CONTINUE handler — cursor NOT FOUND signal

DECLARE v_done INT DEFAULT FALSE;

-- Fires when a SELECT INTO finds no rows, or a cursor runs out of rows
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = TRUE;

Handle a specific error code

-- 1062 = Duplicate entry (unique constraint violation)
DECLARE CONTINUE HANDLER FOR 1062
    SET @dup_error = 'Duplicate key — record already exists';

6. Cursors — Row-by-Row Processing

A cursor lets you iterate over a result set one row at a time inside a procedure. Use them reluctantly — set-based SQL is almost always faster. But for logic that genuinely can't be expressed as a single query (e.g. each row triggers a different branching action), cursors are the right tool.

DELIMITER $$

CREATE PROCEDURE sp_apply_bulk_discount(
    IN  p_genre_name   VARCHAR(80),
    IN  p_discount_pct DECIMAL(5,2),
    OUT p_books_updated INT
)
BEGIN
    DECLARE v_done    BOOLEAN       DEFAULT FALSE;
    DECLARE v_book_id INT;
    DECLARE v_price   DECIMAL(10,2);
    SET p_books_updated = 0;

    -- 1. Declare the cursor
    DECLARE cur_books CURSOR FOR
        SELECT b.book_id, b.price
        FROM      books       b
        JOIN      book_genres bg ON bg.book_id = b.book_id
        JOIN      genres      g  ON g.genre_id = bg.genre_id
        WHERE     g.name = p_genre_name;

    -- 2. Handler fires when the cursor has no more rows
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- 3. Open the cursor
    OPEN cur_books;

    read_loop: LOOP
        -- 4. Fetch one row
        FETCH cur_books INTO v_book_id, v_price;

        IF v_done THEN
            LEAVE read_loop;
        END IF;

        -- 5. Row-level logic
        UPDATE books
        SET    price = ROUND(v_price * (1 - p_discount_pct / 100), 2)
        WHERE  book_id = v_book_id;

        SET p_books_updated = p_books_updated + 1;
    END LOOP;

    -- 6. Close the cursor
    CLOSE cur_books;
END$$

DELIMITER ;

CALL sp_apply_bulk_discount('Fantasy', 15, @updated);
SELECT @updated AS books_discounted;

Cursor order: DECLARE variables → DECLARE cursor → DECLARE handlers → statements. The order matters — MySQL enforces it. Handlers must be declared after cursors.

7. CREATE FUNCTION

A stored function returns exactly one value and can be used anywhere a built-in function can — in SELECT lists, WHERE clauses, ORDER BY, computed column defaults. It's the right choice for reusable logic that produces a single derived value.

CREATE [OR REPLACE]
FUNCTION function_name ([param datatype [, …]])
RETURNS datatype
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA | NO SQL | CONTAINS SQL]
BEGIN
    -- body
    RETURN value;
END

DETERMINISTIC means the function always returns the same output for the same input. Mark it DETERMINISTIC when true — the optimiser can cache results and binary logging is safer. If your function reads from a table it is NOT DETERMINISTIC (data can change between calls). MySQL requires you to declare one or the other.

Function — customer segment label

DELIMITER $$

CREATE OR REPLACE FUNCTION fn_customer_segment(p_lifetime_value DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_segment VARCHAR(20);

    IF     p_lifetime_value >= 200 THEN SET v_segment = 'VIP';
    ELSEIF p_lifetime_value >=  75 THEN SET v_segment = 'Regular';
    ELSE                             SET v_segment = 'Casual';
    END IF;

    RETURN v_segment;
END$$

DELIMITER ;

Now it works anywhere an expression is valid:

-- Use in SELECT list
SELECT
    customer,
    lifetime_value,
    fn_customer_segment(lifetime_value) AS segment
FROM   v_customer_ltv
ORDER BY lifetime_value DESC;

-- Use in WHERE
SELECT customer, lifetime_value
FROM   v_customer_ltv
WHERE  fn_customer_segment(lifetime_value) = 'VIP';

-- Use in ORDER BY
SELECT * FROM v_customer_ltv
ORDER BY fn_customer_segment(lifetime_value), lifetime_value DESC;

Function — formatted price with currency symbol

DELIMITER $$

CREATE OR REPLACE FUNCTION fn_format_price(
    p_amount   DECIMAL(10,2),
    p_currency CHAR(3)
)
RETURNS VARCHAR(20)
DETERMINISTIC NO SQL
BEGIN
    DECLARE v_symbol CHAR(3);
    SET v_symbol = CASE p_currency
        WHEN 'GBP' THEN '£'
        WHEN 'USD' THEN '$'
        WHEN 'EUR' THEN '€'
        ELSE p_currency
    END;
    RETURN CONCAT(v_symbol, FORMAT(p_amount, 2));
END$$

DELIMITER ;

SELECT title, fn_format_price(price, 'GBP') AS display_price
FROM   books
ORDER BY price DESC;

8. Inspecting and Dropping Stored Code

-- List all procedures in the current database
SHOW PROCEDURE STATUS WHERE Db = DATABASE()\G

-- List all functions
SHOW FUNCTION STATUS WHERE Db = DATABASE()\G

-- Show a procedure's full definition
SHOW CREATE PROCEDURE sp_place_order\G

-- Show a function's full definition
SHOW CREATE FUNCTION fn_customer_segment\G

-- information_schema alternative
SELECT routine_name, routine_type, created, last_altered
FROM   information_schema.routines
WHERE  routine_schema = DATABASE()
ORDER BY routine_type, routine_name;

-- Drop
DROP PROCEDURE IF EXISTS sp_place_order;
DROP FUNCTION  IF EXISTS fn_customer_segment;

9. Calling from Application Code

Python (mysql-connector-python)

import mysql.connector

conn   = mysql.connector.connect(host='localhost', database='bookshop',
                                  user='admin', password='secret')
cursor = conn.cursor()

# Call a procedure with IN parameters and a result set
cursor.callproc('sp_books_by_genre', ('Science Fiction',))
for result in cursor.stored_results():
    for row in result.fetchall():
        print(row)

# Call a procedure with OUT parameters
# Pass None for OUT params; retrieve via the returned args tuple
args = (7, None, None)   # customer_id, order_count (OUT), total_spent (OUT)
results = cursor.callproc('sp_customer_total', args)
order_count, total_spent = results[1], results[2]

cursor.close()
conn.close()

PHP (PDO)

$pdo = new PDO('mysql:host=localhost;dbname=bookshop', 'admin', 'secret');

// Call a stored function — just use it in SQL
$stmt = $pdo->prepare('SELECT fn_customer_segment(:ltv)');
$stmt->execute([':ltv' => 250.00]);
$segment = $stmt->fetchColumn();

// Call a procedure with OUT params via user variables
$pdo->exec('CALL sp_customer_total(7, @cnt, @total)');
$row = $pdo->query('SELECT @cnt AS order_count, @total AS total_spent')->fetch();

10. When to Use Stored Code vs Application Logic

Use stored procedures when: Multiple different applications (PHP web app, Python ETL, reporting tool) all need the same workflow — centralise it in the database so each caller doesn't re-implement it. Also good for batch jobs that run entirely inside the database, and for encapsulating privileged operations (a low-privilege app user can CALL a procedure without needing direct table access).

Use stored functions when: You have a reusable calculation or formatting rule that should work like a built-in MySQL function — customer segment labels, price formatting, age-from-date, business day calculation.

Avoid stored code when: Your team's primary language is not SQL (stored procedures are harder to test, version-control, and debug than application code). Complex business logic buried in procedures is difficult to unit test, impossible to profile with standard tools, and invisible to application-layer observability (tracing, logging). Prefer application code for logic that changes frequently or needs thorough test coverage.

Never put secrets or API calls in stored procedures. The database has no HTTP client, no secret manager, and no safe way to handle credentials. Business logic that needs to call external services belongs in application code, not the database.

Quick Reference

Concept	Syntax / rule	Notes
Delimiter	`DELIMITER $$` … `DELIMITER ;`	CLI / Workbench only. Not needed in application code.
Create procedure	`CREATE PROCEDURE name(IN p1 type, OUT p2 type) BEGIN … END`	Use `OR REPLACE` (MySQL 8.0.29+) or DROP first.
Call procedure	`CALL name(arg1, @out_var);`	OUT/INOUT params take user variables (prefixed @).
Read OUT result	`SELECT @out_var;`	User variables persist for the session after CALL returns.
Local variable	`DECLARE v_name type DEFAULT value;`	Must be first statements in BEGIN block. Scoped to block.
Assign variable	`SET v_name = expr;` or `SELECT col INTO v_name FROM …`	SELECT INTO assigns to local variables, not user variables.
Create function	`CREATE FUNCTION name(p type) RETURNS type DETERMINISTIC BEGIN … RETURN val; END`	Must declare DETERMINISTIC or NOT DETERMINISTIC.
Error handler	`DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END;`	EXIT exits the block; CONTINUE resumes after the error.
Cursor lifecycle	DECLARE → OPEN → FETCH loop (check NOT FOUND) → CLOSE	Declare NOT FOUND handler before opening cursor.
Flow control	`IF … ELSEIF … ELSE … END IF` · `CASE … END CASE` · `WHILE … END WHILE` · `REPEAT … UNTIL … END REPEAT` · labelled `LOOP … END LOOP`	`LEAVE label` = break · `ITERATE label` = continue
Inspect	`SHOW PROCEDURE STATUS WHERE Db = DATABASE();` · `SHOW CREATE PROCEDURE name;`	Also available via `information_schema.routines`.
Drop	`DROP PROCEDURE IF EXISTS name;` · `DROP FUNCTION IF EXISTS name;`	Always use IF EXISTS in scripts.

Next: Chapter 9 — Triggers and scheduled events: BEFORE/AFTER INSERT/UPDATE/DELETE triggers, CREATE EVENT, practical use cases and the dangers to watch for.