Stored Procedures Basics

Learning Focus

Use this lesson to understand stored procedures — server-side SQL programs that encapsulate multi-step workflows, accept parameters, and can be called repeatedly by name.

Concept Overview

What Is a Stored Procedure?

A stored procedure is a named block of SQL statements stored on the MySQL server. Instead of sending multiple SQL statements from your application, you call the procedure by name and it executes the entire workflow on the server side.

-- Instead of 5 separate SQL calls from your app:
CALL process_order(1024, 250.00);

Why Use Stored Procedures?

Benefit	Explanation
Reduce network round trips	One CALL instead of multiple queries
Enforce consistent logic	The same procedure runs identically everywhere
Centralize critical workflows	Business rules live in one place, not scattered across services
Security	Grant EXECUTE on a procedure without granting direct table access
Transaction boundaries	Wrap multiple operations in a single transaction inside the procedure

When NOT to Use Stored Procedures

Stored procedures aren't always the right choice:

• Complex business logic — hard to test, version control, and debug compared to application code
• Heavy computation — MySQL is not designed for CPU-intensive work; keep that in your app
• Rapid iteration — changing procedures requires database migrations; app code is faster to deploy

Stored Procedures vs. Functions

Feature	Procedure	Function
Called with	CALL proc_name()	Used in expressions: SELECT func_name()
Return value	Uses OUT parameters	Returns a single value
Side effects	Can modify data (INSERT, UPDATE, DELETE)	Should not modify data
Use in SELECT	❌	✅

---

Basic Syntax & Rules

DELIMITER — Why and How

MySQL normally uses ; to end statements. But stored procedures contain multiple ; inside them. You need to temporarily change the delimiter so MySQL doesn't think the procedure body ends at the first ;.

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL statements here;
    -- Each ends with ; as normal
END //

DELIMITER ;

Parameters: IN, OUT, INOUT

Mode	Direction	Purpose
IN (default)	Caller → Procedure	Pass a value into the procedure
OUT	Procedure → Caller	Return a value to the caller
INOUT	Both ways	Pass a value in AND get a modified value back

DELIMITER //

CREATE PROCEDURE get_customer_total(
    IN  p_customer_id BIGINT,
    OUT p_total        DECIMAL(12,2)
)
BEGIN
    SELECT SUM(total_amount) INTO p_total
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'paid';
END //

DELIMITER ;

Calling a Procedure

-- Simple call
CALL get_customer_total(1024, @total);
SELECT @total AS customer_lifetime_value;

Variables

DELIMITER //

CREATE PROCEDURE example_variables()
BEGIN
    DECLARE order_count INT DEFAULT 0;
    DECLARE avg_amount  DECIMAL(10,2);

    SELECT COUNT(*), AVG(total_amount)
    INTO order_count, avg_amount
    FROM orders;

    SELECT order_count, avg_amount;
END //

DELIMITER ;

---

Step-by-Step Examples

Example 1: Simple Report Procedure

Step 1: Create a procedure that returns customer summary data

DELIMITER //

CREATE PROCEDURE get_customer_summary(IN p_customer_id BIGINT)
BEGIN
    SELECT
        c.full_name,
        c.email,
        COUNT(o.order_id)    AS total_orders,
        SUM(o.total_amount)  AS lifetime_spend,
        MAX(o.order_date)    AS last_order_date
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.customer_id
    WHERE c.customer_id = p_customer_id
    GROUP BY c.customer_id, c.full_name, c.email;
END //

DELIMITER ;

Step 2: Call it

CALL get_customer_summary(1024);

full_name	email	total_orders	lifetime_spend	last_order_date
Alice Rahman	alice@example.com	15	4250.00	2026-02-09

---

Example 2: Multi-Step Workflow — Process an Order

Scenario: Processing an order involves multiple steps: validate stock, create the order, update inventory, and log the event. A procedure encapsulates all of this.

DELIMITER //

CREATE PROCEDURE process_order(
    IN  p_customer_id BIGINT,
    IN  p_product_id  BIGINT,
    IN  p_quantity     INT,
    OUT p_order_id     BIGINT
)
BEGIN
    DECLARE v_price     DECIMAL(10,2);
    DECLARE v_stock     INT;
    DECLARE v_total     DECIMAL(12,2);

    -- Step 1: Check stock
    SELECT price, stock_quantity
    INTO v_price, v_stock
    FROM products
    WHERE product_id = p_product_id;

    IF v_stock < p_quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;

    -- Step 2: Calculate total
    SET v_total = v_price * p_quantity;

    -- Step 3: Create the order
    START TRANSACTION;

    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (p_customer_id, v_total, 'pending');

    SET p_order_id = LAST_INSERT_ID();

    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (p_order_id, p_product_id, p_quantity, v_price);

    -- Step 4: Reduce inventory
    UPDATE products
    SET stock_quantity = stock_quantity - p_quantity
    WHERE product_id = p_product_id;

    -- Step 5: Log the event
    INSERT INTO audit_logs (event_type, details)
    VALUES ('ORDER_CREATED', CONCAT('Order #', p_order_id));

    COMMIT;
END //

DELIMITER ;

Calling it:

CALL process_order(1024, 5, 2, @new_order_id);
SELECT @new_order_id;

---

Example 3: Managing Procedures

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

-- View procedure source code
SHOW CREATE PROCEDURE process_order\G

-- Drop a procedure
DROP PROCEDURE IF EXISTS process_order;

---

Practical Use Cases

1. Order Processing

Validate, create, and update multiple tables atomically in a single CALL.

2. User Registration

Hash password, create user, assign default role, send welcome notification — all in one procedure.

3. Batch Data Processing

Process large datasets in server-side loops without transferring millions of rows to the application.

4. Access Control

Grant EXECUTE on procedures without granting direct table access — users interact through procedures only.

5. Nightly Maintenance

Archival, cleanup, summarization — wrap in a procedure and call from the event scheduler.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Forgetting DELIMITER	Syntax error — procedure body cut off at first ;	Always set DELIMITER // before and DELIMITER ; after
No transaction in multi-step procedures	Partial execution — some steps succeed, others fail	Wrap related operations in START TRANSACTION / COMMIT
No error handling	Errors crash the procedure silently	Use DECLARE ... HANDLER (covered in lesson 3)
God procedures (too much logic)	Hard to test, debug, and maintain	Break complex logic into smaller procedures
Not version-controlling procedures	Lost during migration or server rebuild	Store procedure DDL in version control alongside app code
Using SELECT for debugging	Results mixed with actual output	Use INSERT INTO debug_log instead for persistent debugging

---

Best Practices

• Name with a verb prefix — process_, get_, calculate_, cleanup_
• Use transactions — wrap multi-statement workflows in explicit transactions
• Validate inputs — check parameters at the start and SIGNAL errors for invalid values
• Keep procedures focused — one procedure, one workflow
• Version control your DDL — store CREATE PROCEDURE scripts in your repository
• Log operations — write audit log entries inside critical procedures
• Use OUT parameters for IDs — return generated IDs so the caller can reference them

---

Hands-On Practice

Exercise 1: Customer Lookup (Easy)

Create a procedure get_customer_orders that takes a customer_id as input and returns all their orders (order_id, total_amount, status, order_date). Call it and verify.

Exercise 2: Stock Update (Medium)

Create a procedure restock_product that takes product_id and quantity_to_add. It should:

1. Verify the product exists (SIGNAL error if not)
2. Update stock_quantity
3. Insert an audit log entry
4. Return the new stock quantity via an OUT parameter

Exercise 3: Refactor to Procedure (Advanced)

Take this multi-step SQL workflow and convert it into a stored procedure:

-- 1. Archive orders older than 1 year
INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < NOW() - INTERVAL 1 YEAR;

-- 2. Delete archived orders
DELETE FROM orders WHERE order_date < NOW() - INTERVAL 1 YEAR LIMIT 10000;

-- 3. Log the operation
INSERT INTO audit_logs (event_type, details) VALUES ('ARCHIVE', 'Archived old orders');

---

Connection to Other Concepts

Related Concept	How It Connects
User-Defined Functions	Functions return values for use in SELECT; procedures execute workflows
Control Flow	IF/ELSE, LOOP, WHILE add logic to procedures (next lesson)
Error Handling	DECLARE HANDLER catches errors inside procedures (next lesson)
Triggers	Triggers auto-execute; procedures execute on demand
Transactions	Procedures are the natural place for transaction boundaries
Event Scheduler	Events can CALL procedures on a schedule

What to Learn Next

• User-Defined Functions — for reusable calculations in SELECT queries

---

Visual Learning Diagram

flowchart LR
    A["Application"] -->|"CALL process_order()"| B["MySQL Server"]
    B --> C["Validate inputs"]
    C --> D["START TRANSACTION"]
    D --> E["INSERT order"]
    E --> F["UPDATE inventory"]
    F --> G["INSERT audit log"]
    G --> H["COMMIT"]
    H -->|"Return order_id"| A

    classDef server fill:#0d6efd,stroke:#fff,color:#fff
    class B,C,D,E,F,G,H server

Quick Reference

-- Create procedure
DELIMITER //
CREATE PROCEDURE proc_name(IN p_id BIGINT, OUT p_result INT)
BEGIN
    SELECT COUNT(*) INTO p_result FROM table WHERE id = p_id;
END //
DELIMITER ;

-- Call procedure
CALL proc_name(1, @result);
SELECT @result;

-- View procedure source
SHOW CREATE PROCEDURE proc_name\G

-- List procedures
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- Drop procedure
DROP PROCEDURE IF EXISTS proc_name;

What's Next

• User-Defined Functions