🔧 Lesson 17: Stored Procedures & Functions

Why Stored Routines?

A stored routine is a named block of SQL statements saved in the database. There are two types:

• Stored Procedure — called with CALL, can return multiple result sets, uses OUT parameters
• Stored Function — called inside a SELECT, returns a single value

Benefits

• Reusability: Write the logic once, call it from anywhere
• Consistency: Everyone uses the same logic — no copy-paste drift
• Security: Grant users access to the procedure without granting access to the underlying tables
• Performance: Stored routines are parsed and compiled once, then cached
• Reduced network traffic: One CALL replaces sending multiple statements from the application

The DELIMITER Problem

Here's a quirk you need to understand before writing any procedure. MySQL uses the semicolon (;) as its default statement delimiter. But a stored procedure contains semicolons inside its body. Without intervention, MySQL would try to execute each line individually instead of treating the whole block as one definition.

The solution: temporarily change the delimiter to something else while creating the procedure.

-- Step 1: Change delimiter to //
DELIMITER //

-- Step 2: Create the procedure (semicolons inside are fine now)
CREATE PROCEDURE my_procedure()
BEGIN
    SELECT 'Hello, World!';   -- This semicolon won't end the CREATE
    SELECT NOW();              -- Neither will this one
END //

-- Step 3: Change delimiter back to ;
DELIMITER ;
                

DELIMITER //    -- Popular choice
DELIMITER $$    -- Also popular
                    

Your First Stored Procedure

Basic Syntax

DELIMITER //

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements go here
END //

DELIMITER ;
                

A Simple Procedure — No Parameters

DELIMITER //

CREATE PROCEDURE get_all_books()
BEGIN
    SELECT b.title, b.price, a.name AS author
    FROM books b
    INNER JOIN authors a ON b.author_id = a.id
    ORDER BY b.title;
END //

DELIMITER ;
                

Calling a Procedure

-- Use CALL to execute a stored procedure
CALL get_all_books();
                

+---------------------+-------+---------------+
| title               | price | author        |
+---------------------+-------+---------------+
| 1984                |  9.99 | George Orwell |
| Animal Farm         |  8.49 | George Orwell |
| Dune                | 14.99 | Frank Herbert |
| Pride and Prejudice |  7.99 | Jane Austen   |
+---------------------+-------+---------------+
                    

The procedure encapsulates the JOIN query — callers don't need to know about the books/authors relationship. They just CALL get_all_books() and get results.

Parameters: IN, OUT, INOUT

Procedures become much more useful when they accept input and produce output through parameters.

IN Parameter (Input)

DELIMITER //

CREATE PROCEDURE get_books_by_author(IN author_name VARCHAR(100))
BEGIN
    SELECT b.title, b.price
    FROM books b
    INNER JOIN authors a ON b.author_id = a.id
    WHERE a.name = author_name;
END //

DELIMITER ;

-- Call it:
CALL get_books_by_author('George Orwell');
                

+-------------+-------+
| title       | price |
+-------------+-------+
| 1984        |  9.99 |
| Animal Farm |  8.49 |
+-------------+-------+
                    

OUT Parameter (Output)

DELIMITER //

CREATE PROCEDURE count_books_by_author(
    IN author_name VARCHAR(100),
    OUT book_count INT
)
BEGIN
    SELECT COUNT(*) INTO book_count
    FROM books b
    INNER JOIN authors a ON b.author_id = a.id
    WHERE a.name = author_name;
END //

DELIMITER ;

-- Call it with a user variable to receive the OUT value:
CALL count_books_by_author('George Orwell', @count);
SELECT @count AS books_by_orwell;
                

+-----------------+
| books_by_orwell |
+-----------------+
|               2 |
+-----------------+
                    

CALL my_procedure(@result);
SELECT @result;
                    

INOUT Parameter

DELIMITER //

CREATE PROCEDURE apply_discount(INOUT price DECIMAL(10,2), IN discount_pct INT)
BEGIN
    SET price = price * (1 - discount_pct / 100);
END //

DELIMITER ;

-- Pass in a price, get back the discounted price:
SET @my_price = 100.00;
CALL apply_discount(@my_price, 20);
SELECT @my_price AS discounted_price;
-- Output: 80.00
                

Multiple Parameters

DELIMITER //

CREATE PROCEDURE get_customer_orders(
    IN cust_id INT,
    OUT order_count INT,
    OUT total_spent DECIMAL(10,2)
)
BEGIN
    SELECT
        COUNT(DISTINCT o.id),
        COALESCE(ROUND(SUM(oi.quantity * b.price), 2), 0)
    INTO order_count, total_spent
    FROM orders o
    LEFT JOIN order_items oi ON o.id = oi.order_id
    LEFT JOIN books b ON oi.book_id = b.id
    WHERE o.customer_id = cust_id;
END //

DELIMITER ;

-- Get Alice's stats (customer_id = 1):
CALL get_customer_orders(1, @orders, @spent);
SELECT @orders AS total_orders, @spent AS total_spent;
                

+--------------+-------------+
| total_orders | total_spent |
+--------------+-------------+
|            2 |       42.96 |
+--------------+-------------+
                    

Variables & SET

Inside a stored procedure, you can declare local variables to hold intermediate values:

DELIMITER //

CREATE PROCEDURE calculate_order_total(IN order_id_param INT)
BEGIN
    -- Declare local variables
    DECLARE subtotal DECIMAL(10,2) DEFAULT 0;
    DECLARE tax_rate DECIMAL(4,2) DEFAULT 0.08;
    DECLARE tax_amount DECIMAL(10,2);
    DECLARE grand_total DECIMAL(10,2);

    -- Calculate subtotal
    SELECT ROUND(SUM(oi.quantity * b.price), 2) INTO subtotal
    FROM order_items oi
    INNER JOIN books b ON oi.book_id = b.id
    WHERE oi.order_id = order_id_param;

    -- Calculate tax and total
    SET tax_amount = ROUND(subtotal * tax_rate, 2);
    SET grand_total = subtotal + tax_amount;

    -- Return results
    SELECT
        subtotal,
        tax_rate * 100 AS tax_pct,
        tax_amount,
        grand_total;
END //

DELIMITER ;

CALL calculate_order_total(1);
                

+----------+---------+------------+-------------+
| subtotal | tax_pct | tax_amount | grand_total |
+----------+---------+------------+-------------+
|    34.97 |    8.00 |       2.80 |       37.77 |
+----------+---------+------------+-------------+
                    

Control Flow: IF, CASE, Loops

Stored procedures support full control flow — branching and looping — making them capable of complex logic.

IF ... THEN ... ELSE

DELIMITER //

CREATE PROCEDURE classify_customer(
    IN cust_id INT,
    OUT tier VARCHAR(20)
)
BEGIN
    DECLARE spent DECIMAL(10,2);

    SELECT COALESCE(SUM(oi.quantity * b.price), 0) INTO spent
    FROM orders o
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN books b ON oi.book_id = b.id
    WHERE o.customer_id = cust_id;

    IF spent >= 100 THEN
        SET tier = 'Gold';
    ELSEIF spent >= 50 THEN
        SET tier = 'Silver';
    ELSEIF spent > 0 THEN
        SET tier = 'Bronze';
    ELSE
        SET tier = 'New';
    END IF;
END //

DELIMITER ;

CALL classify_customer(1, @tier);
SELECT @tier;  -- 'Bronze' (Alice spent $42.96)
                

CASE Statement

DELIMITER //

CREATE PROCEDURE get_price_category(
    IN book_id_param INT,
    OUT category VARCHAR(20)
)
BEGIN
    DECLARE book_price DECIMAL(10,2);

    SELECT price INTO book_price FROM books WHERE id = book_id_param;

    CASE
        WHEN book_price >= 20 THEN SET category = 'Premium';
        WHEN book_price >= 10 THEN SET category = 'Standard';
        ELSE SET category = 'Budget';
    END CASE;
END //

DELIMITER ;
                

WHILE Loop

DELIMITER //

CREATE PROCEDURE generate_sample_dates(IN num_days INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE sample_date DATE;

    -- Create a temp table to hold results
    DROP TEMPORARY TABLE IF EXISTS temp_dates;
    CREATE TEMPORARY TABLE temp_dates (day_date DATE);

    WHILE i < num_days DO
        SET sample_date = DATE_ADD(CURRENT_DATE, INTERVAL i DAY);
        INSERT INTO temp_dates (day_date) VALUES (sample_date);
        SET i = i + 1;
    END WHILE;

    SELECT * FROM temp_dates;
    DROP TEMPORARY TABLE temp_dates;
END //

DELIMITER ;

CALL generate_sample_dates(5);
                

+------------+
| day_date   |
+------------+
| 2026-04-16 |
| 2026-04-17 |
| 2026-04-18 |
| 2026-04-19 |
| 2026-04-20 |
+------------+
                    

Other Loop Types

-- REPEAT ... UNTIL (runs at least once)
REPEAT
    SET i = i + 1;
UNTIL i >= 10
END REPEAT;

-- LOOP with LEAVE (break)
my_loop: LOOP
    SET i = i + 1;
    IF i >= 10 THEN
        LEAVE my_loop;
    END IF;
END LOOP;
                

Stored Functions

A stored function returns a single value and can be used directly inside SQL expressions — in SELECT lists, WHERE clauses, and ORDER BY.

Basic Syntax

DELIMITER //

CREATE FUNCTION function_name (parameters)
RETURNS data_type
DETERMINISTIC  -- or NOT DETERMINISTIC
BEGIN
    -- logic
    RETURN value;
END //

DELIMITER ;
                

Example: Tax Calculator

DELIMITER //

CREATE FUNCTION calc_tax(amount DECIMAL(10,2), tax_rate DECIMAL(4,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(amount * tax_rate, 2);
END //

DELIMITER ;

-- Use it in a SELECT:
SELECT
    title,
    price,
    calc_tax(price, 0.08) AS tax,
    price + calc_tax(price, 0.08) AS total
FROM books;
                

+---------------------+-------+------+-------+
| title               | price | tax  | total |
+---------------------+-------+------+-------+
| Dune                | 14.99 | 1.20 | 16.19 |
| 1984                |  9.99 | 0.80 | 10.79 |
| Pride and Prejudice |  7.99 | 0.64 |  8.63 |
| Animal Farm         |  8.49 | 0.68 |  9.17 |
+---------------------+-------+------+-------+
                    

Example: Customer Full Name

DELIMITER //

CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
    RETURN CONCAT(first, ' ', last);
END //

DELIMITER ;

-- Use it anywhere:
SELECT full_name(first_name, last_name) AS customer
FROM customers;
                

Example: Function That Reads Data

DELIMITER //

CREATE FUNCTION get_author_revenue(author_id_param INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE revenue DECIMAL(10,2);

    SELECT COALESCE(ROUND(SUM(oi.quantity * b.price), 2), 0) INTO revenue
    FROM books b
    INNER JOIN order_items oi ON b.id = oi.book_id
    WHERE b.author_id = author_id_param;

    RETURN revenue;
END //

DELIMITER ;

-- Use it in queries:
SELECT name, get_author_revenue(id) AS revenue
FROM authors
ORDER BY revenue DESC;
                

+----------------+---------+
| name           | revenue |
+----------------+---------+
| Frank Herbert  |   29.98 |
| George Orwell  |   19.98 |
| Jane Austen    |    7.99 |
| Isaac Asimov   |    0.00 |
+----------------+---------+
                    

Procedures vs Functions

Managing Stored Routines

View Existing Routines

-- List all procedures in the current database
SHOW PROCEDURE STATUS WHERE Db = 'bookstore';

-- List all functions in the current database
SHOW FUNCTION STATUS WHERE Db = 'bookstore';

-- See the full definition of a procedure
SHOW CREATE PROCEDURE get_all_books;

-- See the full definition of a function
SHOW CREATE FUNCTION calc_tax;
                

Drop a Routine

-- Drop a procedure
DROP PROCEDURE IF EXISTS get_all_books;

-- Drop a function
DROP FUNCTION IF EXISTS calc_tax;
                

Modify a Routine

MySQL doesn't have ALTER PROCEDURE ... AS to change the body. To modify a routine, drop it and recreate it:

-- The standard pattern for updating a procedure:
DROP PROCEDURE IF EXISTS get_all_books;

DELIMITER //
CREATE PROCEDURE get_all_books()
BEGIN
    -- Updated logic here
    SELECT b.title, b.price, a.name AS author
    FROM books b
    INNER JOIN authors a ON b.author_id = a.id
    ORDER BY a.name, b.title;
END //
DELIMITER ;
                

Best Practices & When to Use

Good Uses for Stored Routines

• Complex business calculations — tax, discounts, tiered pricing
• Data validation logic — check constraints beyond what CHECK can do
• Reporting queries — encapsulate complex JOINs and aggregates
• Admin tasks — archive old data, clean up orphaned records
• Security layer — grant EXECUTE on the procedure, not SELECT on the tables

When to Keep Logic in Your Application

• Business rules that change frequently — deploying application code is usually faster than updating database routines
• Complex string manipulation — Python/JS/PHP have much richer string libraries
• HTTP calls, file I/O, external APIs — the database can't do these
• Team doesn't know SQL well — hidden logic in the database becomes a maintenance nightmare

Naming Conventions

• Procedures: verb-first — get_customer_orders, calculate_tax, archive_old_orders
• Functions: describe the return value — calc_tax, full_name, get_author_revenue
• Prefix parameter names to avoid clashing with column names: p_customer_id or cust_id_param

-- ❌ BAD: 'id' could mean the parameter or the column
CREATE PROCEDURE get_customer(IN id INT) ...

-- ✅ GOOD: Clear distinction
CREATE PROCEDURE get_customer(IN cust_id INT) ...
CREATE PROCEDURE get_customer(IN p_id INT) ...
                    

Exercises

-- 1
DELIMITER //
CREATE PROCEDURE get_all_customers()
BEGIN
    SELECT id, first_name, last_name, email
    FROM customers
    ORDER BY last_name, first_name;
END //
DELIMITER ;

-- 2
DELIMITER //
CREATE PROCEDURE get_orders_by_customer(IN cust_id INT)
BEGIN
    SELECT o.id AS order_id, o.order_date
    FROM orders o
    WHERE o.customer_id = cust_id
    ORDER BY o.order_date DESC;
END //
DELIMITER ;

-- 3
DELIMITER //
CREATE PROCEDURE count_orders(IN cust_id INT, OUT order_count INT)
BEGIN
    SELECT COUNT(*) INTO order_count
    FROM orders
    WHERE customer_id = cust_id;
END //
DELIMITER ;

-- Test:
CALL count_orders(1, @cnt);
SELECT @cnt;  -- 2
                        

-- 1
DELIMITER //
CREATE FUNCTION discount_price(
    original DECIMAL(10,2),
    discount_pct INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(original * (1 - discount_pct / 100), 2);
END //
DELIMITER ;

-- 2
DELIMITER //
CREATE FUNCTION order_item_total(item_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT ROUND(oi.quantity * b.price, 2) INTO total
    FROM order_items oi
    INNER JOIN books b ON oi.book_id = b.id
    WHERE oi.id = item_id;
    RETURN COALESCE(total, 0);
END //
DELIMITER ;

-- 3
SELECT
    title,
    price AS original_price,
    discount_price(price, 15) AS sale_price
FROM books;
                        

DELIMITER //
CREATE PROCEDURE categorize_books()
BEGIN
    SELECT
        title,
        price,
        CASE
            WHEN price >= 15 THEN 'Premium'
            WHEN price >= 10 THEN 'Standard'
            ELSE 'Budget'
        END AS price_tier
    FROM books
    ORDER BY price DESC;
END //
DELIMITER ;

CALL categorize_books();
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — CREATE PROCEDURE
• MySQL Docs — CREATE FUNCTION
• MySQL Docs — Flow Control Statements
• MySQL Docs — DECLARE Local Variable

🚀 What's Next?

Stored procedures let you build reusable logic, but what happens when a procedure needs to make multiple changes that must either all succeed or all fail? Transferring money between accounts, for example — you can't debit one account without crediting another. In Lesson 18: Transactions, you'll learn to group operations with BEGIN, COMMIT, and ROLLBACK for bulletproof data integrity.