MySQL DECLARE CONDITION

Master MySQL DECLARE CONDITION for naming error codes and SQLSTATE values — make stored procedure code self-documenting. Covers the syntax, common error codes worth naming (1051, 1062, 1146, 1452), the strict declaration order (variables → conditions → cursors → handlers), and worked examples with multiple named conditions.

When a SQL statement fails or produces a warning inside a stored program, MySQL identifies what happened with one of two things: a MySQL error code (like 1051) or an SQLSTATE value (a 5-character string like '42S02'). These work fine for the database, but they're cryptic for humans reading the code. Was 1051 "unknown table", "deadlock", or "out of disk space"? You'd have to look it up.

The DECLARE ... CONDITION statement lets you give an error code a meaningful name — unknown_table, duplicate_key, foreign_key_violation. Then your handlers reference the name instead of the code, making the procedure self-documenting.

Where condition_value is one of:

The DECLARE ... CONDITION statement only names a condition — it doesn't react to one. You still need a separate DECLARE ... HANDLER (covered on the next page) to actually do something when the condition fires.

Compare the two snippets below. They behave identically, but the second is much clearer:

DECLARE CONTINUE HANDLER FOR 1051
BEGIN
    -- handler body
END;

DECLARE unknown_table CONDITION FOR 1051;

DECLARE CONTINUE HANDLER FOR unknown_table
BEGIN
    -- handler body
END;

The cost is two extra lines; the benefit is that anyone reading the code learns immediately that this handler catches the "unknown table" error. Useful when you have several handlers in one procedure and need to keep track of what each one does.

Build a procedure that takes a table name as input and returns its rows via dynamic SQL. If the table doesn't exist, the named condition catches the error gracefully:

DROP PROCEDURE IF EXISTS GetData;

DELIMITER $$

CREATE PROCEDURE GetData(IN tbl_name VARCHAR(255))
    READS SQL DATA
BEGIN
    -- 1. Variables (none here)
    -- 2. Named conditions
    DECLARE unknown_table CONDITION FOR 1051;

    -- 3. Handlers
    DECLARE EXIT HANDLER FOR unknown_table
    BEGIN
        SELECT CONCAT('Table not found: ', tbl_name) AS error_message;
    END;

    -- Build and run the dynamic query
    SET @sql_query = CONCAT('SELECT * FROM ', tbl_name);
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

Try it with an existing table — note this assumes the employees table from earlier chapters is loaded:

CALL GetData('employees');

Now try a table that doesn't exist:

CALL GetData('abc');

Without the handler, the procedure would have terminated with raw error 1051. The handler catches the named condition and returns a friendly message instead.

You can declare several conditions and handle each differently. Useful when one procedure needs to recognize multiple specific errors:

DROP PROCEDURE IF EXISTS SafeInsertUser;

DELIMITER $$

CREATE PROCEDURE SafeInsertUser(IN p_username VARCHAR(50),
                                IN p_email    VARCHAR(50))
    MODIFIES SQL DATA
BEGIN
    -- Named conditions
    DECLARE duplicate_key   CONDITION FOR 1062;
    DECLARE bad_value       CONDITION FOR 1366;
    DECLARE generic_error   CONDITION FOR SQLEXCEPTION;

    -- Specific handlers (order doesn't matter; MySQL picks the most specific)
    DECLARE EXIT HANDLER FOR duplicate_key
        SELECT 'Username already exists' AS error_message;

    DECLARE EXIT HANDLER FOR bad_value
        SELECT 'Invalid value for one or more columns' AS error_message;

    -- Catch-all
    DECLARE EXIT HANDLER FOR generic_error
        SELECT 'Unexpected database error' AS error_message;

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

DELIMITER ;

This shows the pattern — each named condition gets its own targeted handler, and the generic_error handler catches anything else. We'll go deeper into handler matching on the next page.

You can declare a named condition for either form. Quick guidance:

1. Use named conditions when an error code appears multiple times in the same procedure — DRY principle, single point of update.
2. Pick names that describe the meaning, not the code. duplicate_key beats err_1062.
3. Declare conditions before handlers — MySQL's strict declaration order requires it.
4. Prefer SQLSTATE over numeric codes when working in code that might run on multiple databases or where you want to catch a category rather than a single error.
5. Combine with handlers from the next page — DECLARE CONDITION on its own does nothing; you need DECLARE HANDLER to react to the condition.

• DECLARE condition_name CONDITION FOR condition_value; assigns a meaningful name to a MySQL error code or SQLSTATE value.
• The condition value can be either a numeric MySQL error code (1051) or an SQLSTATE string ('42S02').
• Named conditions don't react to errors on their own — they're referenced by DECLARE HANDLER statements, covered on the next page.
• Declaration order is strict: variables → conditions → cursors → handlers.
• Use named conditions to make procedure code self-documenting; DECLARE EXIT HANDLER FOR unknown_table reads better than DECLARE EXIT HANDLER FOR 1051.