Chapter 8

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.

Stored Procedure
  • 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
Stored Function
  • 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

ConceptSyntax / ruleNotes
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.