Advanced Stored Procedures: Error Handling & Performance

1 Error Handling in Stored Procedures

Building on Exception Handling and Stored Procedures basics, let's implement robust error handling patterns.

The Complete Pattern: Transaction + Error Handler + Logging

MySQL

DELIMITER //
CREATE PROCEDURE process_order(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_status VARCHAR(50)
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_error_msg VARCHAR(500);

    -- Error handler: rollback and log
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_error_msg = MESSAGE_TEXT;
        ROLLBACK;
        INSERT INTO error_log (procedure_name, error_message, created_at)
        VALUES ('process_order', v_error_msg, NOW());
        SET p_status = CONCAT('ERROR: ', v_error_msg);
    END;

    START TRANSACTION;

    -- Check stock
    SELECT stock, price INTO v_stock, v_price
    FROM products WHERE id = p_product_id FOR UPDATE;

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

    -- Deduct stock and create order
    UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;
    INSERT INTO orders (customer_id, product_id, quantity, amount)
    VALUES (p_customer_id, p_product_id, p_quantity, v_price * p_quantity);

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

This is the production pattern: (1) DECLARE HANDLER catches errors, (2) ROLLBACK undoes partial work, (3) Log the error for debugging, (4) Return a meaningful status to the caller. Every critical procedure should follow this template.

Key Takeaways

Always wrap critical logic in START TRANSACTION + error handler
Use GET DIAGNOSTICS to capture the actual error message
Log errors to a table for post-mortem debugging
Use SIGNAL SQLSTATE '45000' for custom business rule violations

2 Performance Considerations

Top

Procedure Performance Tips

Tip	Why
Avoid cursors — use set-based operations	Cursors process row-by-row; `UPDATE ... WHERE` processes all matching rows at once (10-100x faster)
Minimize round-trips	Batch multiple operations into one procedure call instead of calling the DB repeatedly from your app
Use temporary tables for complex logic	Store intermediate results to avoid recalculating
Avoid `SELECT *` inside procedures	Fetch only the columns you need
Keep transactions short	Long transactions hold locks and block other users

Bad vs Good

-- BAD: Cursor processes row by row (slow)
DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending';
-- ... loop through each row and UPDATE one at a time

-- GOOD: Set-based operation (fast)
UPDATE orders SET status = 'expired'
WHERE status = 'pending'
  AND order_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY);

The #1 procedure performance killer: Using cursors to loop through rows when a single UPDATE ... WHERE would do the same thing. SQL is designed for set-based operations. Think in sets, not loops.

Key Takeaways

Avoid cursors — use UPDATE/DELETE ... WHERE for batch operations
Batch work into single procedure calls to reduce network round-trips
Keep transactions as short as possible to minimize lock duration
Think in sets, not loops — SQL is built for set operations

Advanced Stored Procedures

In This Chapter

1 Error Handling in Stored Procedures

The Complete Pattern: Transaction + Error Handler + Logging

Key Takeaways

2 Performance Considerations

Procedure Performance Tips

Key Takeaways

All Chapters in SQL Advanced Course

Courses

Services

Support

Connect