A procedure without error handling is like a car without brakes. It might work fine on a straight road, but the moment something goes wrong — and something always goes wrong — it crashes. In production, your procedure will face NULL values, constraint violations, deadlocks, and duplicate keys. If you have not planned for these, your data is at risk.
| Mechanism | What It Does | When to Use |
|---|---|---|
| DECLARE HANDLER | Catches exceptions and defines what to do | Catch duplicate key, foreign key violations, generic errors |
| SIGNAL SQLSTATE | Raises a custom error from your code | Business rule violations — insufficient funds, invalid status |
| Transaction ROLLBACK | Undoes all changes if something fails | Any multi-step operation where partial data is dangerous |
DELIMITER //
CREATE PROCEDURE create_user(
IN p_email VARCHAR(100),
IN p_name VARCHAR(100),
OUT p_result VARCHAR(100)
)
BEGIN
-- Handler for duplicate key (email already exists)
DECLARE EXIT HANDLER FOR 1062
BEGIN
SET p_result = 'FAILED: Email already exists';
END;
-- Handler for any other SQL exception
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_result = 'FAILED: Unexpected database error';
END;
INSERT INTO users (email, full_name, created_at)
VALUES (p_email, p_name, NOW());
SET p_result = 'SUCCESS';
END //
DELIMITER ;CONTINUE HANDLER vs EXIT HANDLER — this is an important distinction. EXIT HANDLER stops execution and jumps out of the BEGIN...END block. CONTINUE HANDLER executes the handler code and then continues with the next statement. In most cases, you want EXIT — because continuing after an error usually makes things worse.
DELIMITER //
CREATE PROCEDURE update_order_status(
IN p_order_id INT,
IN p_new_status VARCHAR(20)
)
BEGIN
DECLARE v_current_status VARCHAR(20);
SELECT status INTO v_current_status
FROM orders WHERE order_id = p_order_id;
-- Business rule: Cannot go backwards in workflow
IF v_current_status = 'DELIVERED' AND p_new_status = 'PROCESSING' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot revert a delivered order to processing';
END IF;
IF v_current_status = 'CANCELLED' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot change status of a cancelled order';
END IF;
-- Valid transitions only
IF NOT (
(v_current_status = 'PENDING' AND p_new_status IN ('PROCESSING', 'CANCELLED')) OR
(v_current_status = 'PROCESSING' AND p_new_status IN ('SHIPPED', 'CANCELLED')) OR
(v_current_status = 'SHIPPED' AND p_new_status IN ('DELIVERED', 'RETURNED'))
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = CONCAT('Invalid transition: ', v_current_status, ' -> ', p_new_status);
END IF;
UPDATE orders SET status = p_new_status, updated_at = NOW()
WHERE order_id = p_order_id;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE process_payroll(
IN p_month INT,
IN p_year INT,
OUT p_employees_processed INT,
OUT p_result VARCHAR(100)
)
BEGIN
DECLARE v_error_occurred BOOLEAN DEFAULT FALSE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET v_error_occurred = TRUE;
ROLLBACK;
SET p_result = 'FAILED: Payroll rolled back due to error';
SET p_employees_processed = 0;
END;
START TRANSACTION;
-- Step 1: Calculate salaries for all employees
INSERT INTO payroll (emp_id, month, year, gross_salary, tax, net_salary)
SELECT emp_id, p_month, p_year,
salary AS gross_salary,
ROUND(salary * 0.30, 2) AS tax,
ROUND(salary * 0.70, 2) AS net_salary
FROM employees
WHERE status = 'ACTIVE';
SET p_employees_processed = ROW_COUNT();
-- Step 2: Check for duplicates (already processed this month)
IF EXISTS (
SELECT emp_id FROM payroll
WHERE month = p_month AND year = p_year
GROUP BY emp_id HAVING COUNT(*) > 1
) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Duplicate payroll detected — rolling back';
END IF;
-- Step 3: Update payroll status
UPDATE payroll_summary
SET status = 'PROCESSED', processed_at = NOW()
WHERE month = p_month AND year = p_year;
COMMIT;
SET p_result = 'SUCCESS';
END //
DELIMITER ;-- Test 1: Duplicate email handler
INSERT INTO users (email, full_name) VALUES ('test@example.com', 'First User');
CALL create_user('test@example.com', 'Duplicate User', @result);
SELECT @result;
-- Expected: 'FAILED: Email already exists'
-- Test 2: Invalid status transition
INSERT INTO orders (order_id, status) VALUES (999, 'DELIVERED');
CALL update_order_status(999, 'PROCESSING');
-- Expected: ERROR — Cannot revert a delivered order to processing
SELECT status FROM orders WHERE order_id = 999;
-- Expected: Still 'DELIVERED'
-- Test 3: Rollback test — all or nothing
-- Force an error mid-way through payroll
CALL process_payroll(13, 2025, @count, @result); -- Invalid month
SELECT @result;
-- Expected: FAILED
SELECT COUNT(*) FROM payroll WHERE month = 13;
-- Expected: 0 — everything rolled backThe most dangerous error handling bug: a CONTINUE HANDLER that swallows errors silently. The procedure finishes with "SUCCESS" but the data is half-written. Always check that EXIT HANDLER is used for critical errors, and that ROLLBACK happens before the handler ends.
Q: How does error handling work in MySQL stored procedures?
A: MySQL uses three mechanisms: (1) DECLARE HANDLER catches exceptions — EXIT HANDLER stops execution, CONTINUE HANDLER resumes after handling. You can catch specific error codes like 1062 (duplicate key) or generic SQLEXCEPTION. (2) SIGNAL SQLSTATE raises custom errors for business rule violations, like SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT = "Insufficient funds". (3) Transaction ROLLBACK undoes all changes when an error occurs, ensuring atomicity. In testing, I verify that: handlers catch the right errors, SIGNAL raises proper error messages, and ROLLBACK leaves no partial data. The most common bug I look for is a CONTINUE HANDLER that silently ignores errors.
Key Point: Error handling has three tools: DECLARE HANDLER to catch errors, SIGNAL SQLSTATE to raise custom errors, and ROLLBACK to undo changes. Always test that errors are caught, messages are correct, and partial data is never left behind.