Skip to content

Advanced Stored Procedures

Master error handling within stored procedures and functions, and learn performance best practices for writing efficient database logic.

2 Topics Chapter 9 of 12 Advanced

In This Chapter

  1. Error Handling in Stored Procedures
  2. Performance Considerations

1 Error Handling in Stored Procedures

Top

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

The Complete Pattern: Transaction + Error Handler + Logging

MySQLDELIMITER // 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 ;
Uses syntax specific to:MySQLRan without errors on:MySQL 8.0.46MariaDB 10.4.32

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

TipWhy
Avoid cursors - use set-based operationsCursors process row-by-row; UPDATE ... WHERE processes all matching rows at once (10-100x faster)
Minimize round-tripsBatch multiple operations into one procedure call instead of calling the DB repeatedly from your app
Use temporary tables for complex logicStore intermediate results to avoid recalculating
Avoid SELECT * inside proceduresFetch only the columns you need
Keep transactions shortLong 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);
Uses syntax specific to:MySQLNot confirmed by our test run

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

Practise this chapter

Exercises, reference pages and interview questions on the topics in Advanced Stored Procedures.

Reference

Interview questions

What's next? Up next is Advanced Triggers. Keep going to build on what you just learned in Advanced Stored Procedures.

BACK TO COURSE
SQL Advanced Course
NEXT CHAPTER
Advanced Triggers