Control Flow and Error Handling

Learning Focus

Use this lesson to understand control flow (IF, CASE, LOOP) and error handling (DECLARE HANDLER, SIGNAL) inside MySQL stored procedures and functions.

Concept Overview

What Is Control Flow?

Control flow statements let your stored routines make decisions and repeat actions:

• IF / ELSEIF / ELSE — choose between code paths based on conditions
• CASE — clean multi-branch alternative to nested IF
• LOOP / WHILE / REPEAT — execute code repeatedly
• LEAVE — break out of a loop or labeled block

What Is Error Handling?

When SQL statements inside a procedure fail (constraint violations, deadlocks, missing data), MySQL raises an error. Without error handling, the procedure crashes and returns a cryptic error to the caller.

Error handling lets you:

• Catch specific errors and respond gracefully
• Roll back transactions when something goes wrong
• Return meaningful error messages instead of raw SQL errors

Key Components

Component	Purpose
IF / ELSEIF / ELSE	Conditional branching
CASE	Multi-value switching
LOOP / WHILE / REPEAT	Iteration
LEAVE label	Break out of a loop or block
DECLARE ... HANDLER	Catch errors (like try/catch)
SIGNAL SQLSTATE	Raise custom errors (like throw)
RESIGNAL	Re-throw a caught error

---

Basic Syntax & Rules

IF / ELSEIF / ELSE

IF condition THEN
    -- statements
ELSEIF condition THEN
    -- statements
ELSE
    -- statements
END IF;

CASE Statement

CASE expression
    WHEN value1 THEN
        -- statements
    WHEN value2 THEN
        -- statements
    ELSE
        -- statements
END CASE;

WHILE Loop

WHILE condition DO
    -- statements
END WHILE;

LOOP with LEAVE

loop_label: LOOP
    -- statements
    IF done THEN
        LEAVE loop_label;
    END IF;
END LOOP loop_label;

DECLARE HANDLER — Error Catching

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    -- handle the error (log it, set a flag, rollback)
END;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;  -- re-throw the error
END;

Handler Type	Behavior
CONTINUE	Catches the error, then continues execution
EXIT	Catches the error, then exits the current BEGIN/END block

SIGNAL — Raising Custom Errors

SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient balance for transfer';

SQLSTATE '45000' is the general "application error" code used for custom business-rule violations.

---

Step-by-Step Examples

Example 1: Balance Transfer with Full Error Handling

This is the canonical example — transferring money between accounts with validation, transactions, and error recovery.

DELIMITER //

CREATE PROCEDURE transfer_balance(
    IN  p_from_account BIGINT,
    IN  p_to_account   BIGINT,
    IN  p_amount       DECIMAL(12,2),
    OUT p_status       VARCHAR(50)
)
BEGIN
    DECLARE v_from_balance DECIMAL(12,2);
    DECLARE v_from_exists  INT DEFAULT 0;
    DECLARE v_to_exists    INT DEFAULT 0;

    -- Error handler: rollback on any SQL error
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Transaction rolled back';
        -- Optionally log the error
        INSERT INTO error_logs (procedure_name, error_message, created_at)
        VALUES ('transfer_balance', p_status, NOW());
    END;

    -- Step 1: Validate inputs
    IF p_amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Transfer amount must be positive';
    END IF;

    -- Step 2: Verify both accounts exist
    SELECT COUNT(*) INTO v_from_exists
    FROM accounts WHERE account_id = p_from_account;

    SELECT COUNT(*) INTO v_to_exists
    FROM accounts WHERE account_id = p_to_account;

    IF v_from_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Source account does not exist';
    END IF;

    IF v_to_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Destination account does not exist';
    END IF;

    -- Step 3: Check sufficient balance
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;  -- Lock the row

    IF v_from_balance < p_amount THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient balance for transfer';
    END IF;

    -- Step 4: Execute transfer within transaction
    START TRANSACTION;

    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    INSERT INTO transfer_logs (from_account, to_account, amount, transferred_at)
    VALUES (p_from_account, p_to_account, p_amount, NOW());

    COMMIT;
    SET p_status = 'SUCCESS';
END //

DELIMITER ;

Calling it:

CALL transfer_balance(1001, 1002, 500.00, @status);
SELECT @status;
-- Result: SUCCESS

CALL transfer_balance(1001, 1002, 999999.00, @status);
SELECT @status;
-- Result: ERROR (insufficient balance triggers SIGNAL → handler rolls back)

---

Example 2: Batch Processing with a Loop

Scenario: Process expired subscriptions in batches.

DELIMITER //

CREATE PROCEDURE expire_subscriptions()
BEGIN
    DECLARE v_rows_affected INT DEFAULT 1;
    DECLARE v_total_expired INT DEFAULT 0;
    DECLARE v_batch_size    INT DEFAULT 1000;

    batch_loop: WHILE v_rows_affected > 0 DO
        UPDATE subscriptions
        SET status = 'expired',
            expired_at = NOW()
        WHERE status = 'active'
          AND end_date < CURDATE()
        LIMIT 1000;

        SET v_rows_affected = ROW_COUNT();
        SET v_total_expired = v_total_expired + v_rows_affected;

        -- Safety: prevent infinite loop
        IF v_total_expired > 100000 THEN
            LEAVE batch_loop;
        END IF;
    END WHILE;

    SELECT v_total_expired AS total_subscriptions_expired;
END //

DELIMITER ;

---

Example 3: CASE Statement for Status Routing

DELIMITER //

CREATE PROCEDURE update_order_status(
    IN p_order_id BIGINT,
    IN p_new_status VARCHAR(20)
)
BEGIN
    CASE p_new_status
        WHEN 'paid' THEN
            UPDATE orders SET status = 'paid', paid_at = NOW()
            WHERE order_id = p_order_id;

        WHEN 'shipped' THEN
            UPDATE orders SET status = 'shipped', shipped_at = NOW()
            WHERE order_id = p_order_id;

        WHEN 'cancelled' THEN
            UPDATE orders SET status = 'cancelled', cancelled_at = NOW()
            WHERE order_id = p_order_id;

            -- Restore inventory
            UPDATE products p
            JOIN order_items oi ON oi.product_id = p.product_id
            SET p.stock_quantity = p.stock_quantity + oi.quantity
            WHERE oi.order_id = p_order_id;

        ELSE
            SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'Invalid order status';
    END CASE;
END //

DELIMITER ;

---

Example 4: Catching Specific Errors

DELIMITER //

CREATE PROCEDURE safe_insert_customer(
    IN  p_email VARCHAR(255),
    IN  p_name  VARCHAR(120),
    OUT p_result VARCHAR(50)
)
BEGIN
    -- Catch duplicate key error specifically
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        SET p_result = 'DUPLICATE_EMAIL';
    END;

    INSERT INTO customers (email, full_name)
    VALUES (p_email, p_name);

    SET p_result = 'SUCCESS';
END //

DELIMITER ;

CALL safe_insert_customer('alice@example.com', 'Alice', @result);
SELECT @result;  -- SUCCESS (first time) or DUPLICATE_EMAIL (if email exists)

---

Practical Use Cases

1. Financial Transfers

Validate, lock, transfer, and log — all wrapped in a transaction with error handling.

2. Batch Processing

Process large datasets in loops with LIMIT to avoid long locks.

3. State Machines

Order status transitions (pending → paid → shipped → delivered) with validation at each step.

4. Input Validation

Use SIGNAL to reject invalid parameters with business-friendly error messages.

5. Retry Logic

CONTINUE handlers catch deadlocks (error 1213), increment a retry counter, and re-attempt the operation.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Missing END IF or END CASE	Syntax error	Every IF needs END IF, every CASE needs END CASE
No error handler + failed operation	Procedure crashes, partial changes persist	Always add an EXIT HANDLER with ROLLBACK for multi-step procedures
Infinite loop	Procedure never finishes, locks tables	Add a counter or condition check with LEAVE
Using CONTINUE handler without checking state	Code continues after error with corrupted state	Use a flag variable to track if an error occurred
SIGNAL without SQLSTATE	Syntax error	Always use SIGNAL SQLSTATE '45000'
Not testing error paths	Errors only discovered in production	Write test cases for each SIGNAL and handler

---

Best Practices

• Validate first, execute second — check all preconditions before START TRANSACTION
• Use EXIT handlers for transactions — ensure ROLLBACK happens on any error
• Add safety limits to loops — prevent infinite loops with a maximum iteration count
• Use SIGNAL for business rules — return meaningful errors instead of letting raw SQL errors reach the application
• Catch specific error codes — HANDLER FOR 1062 (duplicate key) is more precise than HANDLER FOR SQLEXCEPTION
• Log errors — insert into an error log table inside the handler for debugging

---

Hands-On Practice

Exercise 1: Conditional Discount (Easy)

Create a procedure apply_discount(IN p_order_id BIGINT, IN p_customer_tier VARCHAR(20)) that:

• Platinum: 15% discount
• Gold: 10% discount
• Silver: 5% discount
• Other: 0%

Use IF/ELSEIF or CASE to determine the discount.

Exercise 2: Safe Batch Delete (Medium)

Create a procedure purge_old_logs(IN p_days_old INT) that:

1. Deletes logs older than p_days_old days in batches of 5000
2. Stops when no more rows match
3. Returns the total number of deleted rows
4. Has a safety limit of 500,000 total deletions

Exercise 3: Retry on Deadlock (Advanced)

Create a procedure that:

1. Attempts to update a row
2. If it encounters a deadlock (error 1213), waits 1 second and retries
3. Gives up after 3 attempts and returns an error message

---

Connection to Other Concepts

Related Concept	How It Connects
Stored Procedures	Control flow and error handling extend procedure capabilities
User-Defined Functions	Functions also use IF/CASE; error handling is less common
Transactions	ROLLBACK inside error handlers is the standard pattern
Deadlocks	CONTINUE handlers can implement retry logic for deadlocks
Triggers	Triggers use the same control flow syntax

What to Learn Next

• 14. Triggers — automatic event-driven execution with similar syntax

---

Visual Learning Diagram

flowchart TD
    A["CALL transfer_balance()"] --> B["Validate inputs"]
    B -->|Invalid| C["SIGNAL error"]
    B -->|Valid| D["START TRANSACTION"]
    D --> E["Execute operations"]
    E -->|Success| F["COMMIT"]
    E -->|SQL Error| G["EXIT HANDLER triggers"]
    G --> H["ROLLBACK"]
    H --> I["Log error"]
    C --> J["Error returned to caller"]
    F --> K["Success returned to caller"]
    I --> J

    classDef error fill:#dc3545,stroke:#fff,color:#fff
    classDef success fill:#28a745,stroke:#fff,color:#fff
    class C,G,H,I,J error
    class F,K success

Quick Reference

-- IF/ELSEIF/ELSE
IF x > 0 THEN ... ELSEIF x = 0 THEN ... ELSE ... END IF;

-- CASE
CASE val WHEN 'a' THEN ... WHEN 'b' THEN ... ELSE ... END CASE;

-- WHILE loop
WHILE condition DO ... END WHILE;

-- LOOP with LEAVE
label: LOOP ... IF done THEN LEAVE label; END IF; END LOOP label;

-- Error handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; END;
DECLARE CONTINUE HANDLER FOR 1062 SET dup = TRUE;

-- Raise custom error
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error message';

What's Next

• 14. Triggers