Error Handling in Stored Procedures

MySQL stored procedures support error handling through DECLARE ... HANDLER statements. Handlers let you catch SQL errors and warnings, take corrective action, and prevent procedures from aborting unexpectedly.

Handler Types

CONTINUE handler: Catches the error and continues execution after the statement that caused it
EXIT handler: Catches the error and exits the innermost BEGIN...END block

Condition Values

SQLEXCEPTION — any SQL error
SQLWARNING — any SQL warning (01xxx)
NOT FOUND — no rows found (02xxx)
Specific SQLSTATE codes, e.g., '23000' for duplicate key

Using SIGNAL

The SIGNAL statement lets you raise a custom error from within a procedure. This is useful for enforcing business rules and returning meaningful error messages to the caller.

Example

DELIMITER //

CREATE PROCEDURE SafeInsertUser(IN p_email VARCHAR(255))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User insert failed, transaction rolled back';
    END;

    START TRANSACTION;
    INSERT INTO users (email, created_at) VALUES (p_email, NOW());
    COMMIT;
END //

DELIMITER ;

SQL

Full Editor

Pro Tip

Use SIGNAL SQLSTATE '45000' to raise custom application-level errors from stored procedures.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned