MySQL DECLARE HANDLER

Master MySQL DECLARE HANDLER for catching errors and warnings inside stored programs. Covers EXIT vs CONTINUE actions, condition values (error codes, SQLSTATE, named conditions, SQLWARNING/NOT FOUND/SQLEXCEPTION shorthands), handler search rules and most-specific-wins behavior, GET DIAGNOSTICS for retrieving error details, and the canonical transactional ROLLBACK + RESIGNAL pattern.

A handler is the piece of code MySQL runs when a specific error or warning occurs inside a stored program. Without a handler, any unhandled SQL exception terminates the procedure immediately and propagates the error to the caller. With a handler, you can catch the error, optionally clean up, log, transform the message, or re-raise — your stored programs gain proper error-handling discipline.

Three pieces:

• Action — what happens after the handler body runs:
  • EXIT — terminate the enclosing BEGIN ... END block
  • CONTINUE — keep running from the next statement after the one that triggered the condition
  • UNDO — defined in the standard but not supported by MySQL; trying to use it gives a syntax error
• Condition list — one or more condition_values separated by commas. The handler fires when any of them matches.
• Statement or block — what to run when the handler triggers. Usually a BEGIN ... END block but can be a single statement.

The condition_value can be any of these forms:

An SQLSTATE is a 5-character string with a defined structure:

Common class codes:

So '42S02' = class 42 (syntax/access) + subclass S02 (table not found); '23000' = class 23 (integrity) + subclass 000 (any).

When a condition fires, MySQL looks for a matching handler in this order:

1. The current BEGIN ... END block — innermost first
2. Outer enclosing blocks — working outward toward the procedure body
3. If no handler matches anywhere, the condition propagates to the caller

Within a single block, MySQL picks the most specific handler:

1. Specific MySQL error code (e.g. FOR 1062)
2. Specific SQLSTATE (e.g. FOR SQLSTATE '23000')
3. Shorthand class (e.g. FOR SQLEXCEPTION)

So if you have handlers for both 1062 and SQLEXCEPTION and a duplicate-key error fires, the 1062 handler runs — not the catch-all.

An EXIT HANDLER exits the enclosing block when a condition fires. Useful when an error means "don't try to continue — stop and report."

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    user_id   INT AUTO_INCREMENT PRIMARY KEY,
    username  VARCHAR(50)  UNIQUE NOT NULL,
    email     VARCHAR(50)  NOT NULL
);

Now a procedure that handles the unique-key violation gracefully. SQLSTATE '23000' covers integrity violations; the duplicate-username case is one example:

DROP PROCEDURE IF EXISTS insert_user;

DELIMITER $$

CREATE PROCEDURE insert_user(IN p_username VARCHAR(50),
                             IN p_email    VARCHAR(50))
    MODIFIES SQL DATA
BEGIN
    -- Catch unique-constraint violations specifically
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SELECT CONCAT('Error: username "', p_username,
                      '" already exists') AS message;
    END;

    -- Try the insert
    INSERT INTO users (username, email) VALUES (p_username, p_email);

    -- This only runs if the insert succeeded
    SELECT 'User inserted successfully' AS message;
END$$

DELIMITER ;

First call with a fresh username succeeds:

CALL insert_user('jane', 'jane@example.com');

Second call with the same username triggers the unique-key violation. The handler catches it and returns the friendly message instead of an error:

CALL insert_user('jane', 'jane2@example.com');

Notice the "User inserted successfully" message didn't appear — that's because EXIT HANDLER exits the enclosing BEGIN ... END block, skipping any statements after the failed INSERT.

The most common CONTINUE HANDLER pattern: detecting end-of-result inside a cursor loop. NOT FOUND is the SQLSTATE class 02, which fires when FETCH exhausts the cursor:

DROP PROCEDURE IF EXISTS sum_user_ids;

DELIMITER $$

CREATE PROCEDURE sum_user_ids(OUT total INT)
    READS SQL DATA
BEGIN
    -- Variables
    DECLARE done    INT DEFAULT 0;
    DECLARE cur_id  INT;

    -- Cursor
    DECLARE c CURSOR FOR SELECT user_id FROM users;

    -- Handler — sets the flag when FETCH runs out
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET total = 0;
    OPEN c;

    read_loop: LOOP
        FETCH c INTO cur_id;
        IF done = 1 THEN LEAVE read_loop; END IF;
        SET total = total + cur_id;
    END LOOP read_loop;

    CLOSE c;
END$$

DELIMITER ;

CALL sum_user_ids(@total);
SELECT @total AS sum_of_ids;

The NOT FOUND handler runs every time FETCH fails — but with CONTINUE action, execution goes back to the loop. The flag tells us when to LEAVE.

You can declare several handlers in one block. MySQL picks the most specific match for each error:

DROP PROCEDURE IF EXISTS insert_user_layered;

DELIMITER $$

CREATE PROCEDURE insert_user_layered(IN p_username VARCHAR(50),
                                     IN p_email    VARCHAR(50))
    MODIFIES SQL DATA
BEGIN
    -- Most specific: duplicate username
    DECLARE EXIT HANDLER FOR 1062
        SELECT 'Username already exists' AS error_message;

    -- Less specific: any data exception
    DECLARE EXIT HANDLER FOR SQLSTATE '22001'
        SELECT 'Value too long for column' AS error_message;

    -- Catch-all
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SELECT 'An unexpected error occurred' AS error_message;

    INSERT INTO users (username, email) VALUES (p_username, p_email);
    SELECT 'Success' AS status;
END$$

DELIMITER ;

If a duplicate-key error (1062) occurs, the first handler wins — even though SQLEXCEPTION would also match. The order of declaration doesn't matter; specificity does.

Inside a handler, you often want to know what went wrong — not just that something did. GET DIAGNOSTICS retrieves the error code, SQLSTATE, and message into local variables for inspection:

DROP PROCEDURE IF EXISTS detailed_error;

DELIMITER $$

CREATE PROCEDURE detailed_error(IN p_username VARCHAR(50),
                                IN p_email    VARCHAR(50))
    MODIFIES SQL DATA
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        DECLARE v_errno     INT;
        DECLARE v_sqlstate  CHAR(5);
        DECLARE v_message   TEXT;

        GET DIAGNOSTICS CONDITION 1
            v_errno    = MYSQL_ERRNO,
            v_sqlstate = RETURNED_SQLSTATE,
            v_message  = MESSAGE_TEXT;

        SELECT v_errno    AS error_code,
               v_sqlstate AS sqlstate,
               v_message  AS message;
    END;

    INSERT INTO users (username, email) VALUES (p_username, p_email);
END$$

DELIMITER ;

CALL detailed_error('jane', 'someone@example.com');    -- duplicate username

The single most important error-handling pattern in MySQL stored procedures: roll back on any error, then re-raise so the caller knows.

CREATE PROCEDURE transactional_work()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;            -- propagate the original error
    END;

    START TRANSACTION;
        -- multi-statement work here
    COMMIT;
END;

The handler catches any SQL exception — duplicate keys, foreign-key violations, deadlocks, anything. It rolls back the transaction (so the database is left consistent), then RESIGNALs the error so the caller knows what failed. We'll see RESIGNAL in detail on the next page.

1. Default to EXIT HANDLER FOR SQLEXCEPTION for transactional procedures — combined with ROLLBACK; RESIGNAL; it gives you safe error handling for free.
2. Use CONTINUE HANDLER FOR NOT FOUND for cursor loops — that's the canonical end-of-cursor signal.
3. Use specific error codes (or named conditions) when you can react meaningfully to a particular error — duplicate username, missing FK parent, etc.
4. Reach for GET DIAGNOSTICS when one generic handler needs to surface details — log them, transform the message, react conditionally.
5. Don't swallow errors silently. A handler that just returns "Error" with no detail makes debugging miserable. Either log the diagnostics, or RESIGNAL.
6. Watch the order: variables → conditions → cursors → handlers in any BEGIN ... END block. MySQL is strict.
7. Don't try to use UNDO — MySQL doesn't support it, despite what the standard says. Use ROLLBACK in an EXIT handler instead.

• DECLARE { EXIT | CONTINUE } HANDLER FOR condition_value [, ...] statement; defines what runs when a condition fires.
• EXIT exits the enclosing BEGIN ... END; CONTINUE resumes after the failing statement; UNDO is in the standard but not implemented in MySQL.
• Condition values can be MySQL error codes, SQLSTATE strings, named conditions, or the shorthand classes SQLWARNING / NOT FOUND / SQLEXCEPTION.
• SQLSTATE is a 5-character code: 2-char class + 3-char subclass. '01...' = warning, '02...' = no data, '45...' = user-defined exception.
• When multiple handlers could match, the most specific one wins — error code beats SQLSTATE beats shorthand class.
• GET DIAGNOSTICS retrieves error details (MYSQL_ERRNO, RETURNED_SQLSTATE, MESSAGE_TEXT) inside a handler — useful for logging and conditional logic.
• The standard transactional pattern: DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;