SQL CONDITION

What is SQL DECLARE CONDITION used for?

DECLARE CONDITION creates a named alias for an SQLSTATE value or MySQL error code inside stored programs so you can reference it with SIGNAL, RESIGNAL, and exception handlers.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CONDITION: MySQL 5.5+, MariaDB 10.3+, Percona Server. Partial in ISO SQL/PSM compliant engines. Not supported in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL CONDITION Full Explanation

DECLARE CONDITION belongs to the SQL/PSM standard and is fully supported in MySQL and MariaDB. It lets you map a descriptive identifier to a specific SQLSTATE string (five-character code) or a MySQL-specific error number. You place DECLARE CONDITION statements at the top of a compound statement block (between BEGIN and executable code) in stored procedures, functions, triggers, or events. After declaration you can: - Raise the condition with SIGNAL condition_name; - Re-raise it with RESIGNAL; - Catch it with DECLARE ... HANDLER FOR condition_name. The association exists only within the block scope. If a nested block declares a condition with the same name, it overrides the outer one. The statement does not create or modify database objects and has no runtime cost until referenced. Caveats - Works only inside stored program blocks, not in ad-hoc SQL. - condition_value must be a valid SQLSTATE literal ('ABCDE') or an integer error code (1000-4999 for MySQL). - Must appear before other non-declare statements in the block.

SQL CONDITION Syntax

DECLARE condition_name CONDITION FOR
    SQLSTATE 'sqlstate_value'
  | mysql_error_code;

SQL CONDITION Parameters

condition_name (Identifier) - Name you will use in SIGNAL/HANDLER statements
sqlstate_value - CHAR (5) - A valid SQLSTATE code such as '02000' or '23000'
mysql_error_code- INT - A MySQL error number such as 1062 (duplicate key)

Example Queries Using SQL CONDITION

-- Example 1: Alias a duplicate key error
BEGIN
  DECLARE dup_key CONDITION FOR SQLSTATE '23000';
  DECLARE EXIT HANDLER FOR dup_key
      ROLLBACK;
  START TRANSACTION;
  INSERT INTO users(id,email) VALUES(1,'a@getgalaxy.io');
  COMMIT;
END;

-- Example 2: Custom application error using SIGNAL
BEGIN
  DECLARE not_authorized CONDITION FOR SQLSTATE '45000';
  IF @role <> 'admin' THEN
     SIGNAL not_authorized
       SET MESSAGE_TEXT = 'Only admins can run this procedure';
  END IF;
END;

Expected Output Using SQL CONDITION

Example 1 rolls back the transaction if an insert raises SQLSTATE 23000
Example 2 immediately raises a user-defined error with text 'Only admins can run this procedure'

Use Cases with SQL CONDITION

Provide human-readable names for cryptic SQLSTATE codes.
Centralize error handling logic in complex procedures.
Raise custom business-logic errors that client code can trap.
Simplify reuse of the same condition in multiple handlers.

Common Mistakes with SQL CONDITION

Treating CONDITION as a WHERE predicate keyword.
Declaring it after executable statements; MySQL will error with 'DECLARE not allowed here'.
Using an invalid five-character SQLSTATE string.
Expecting the alias to persist outside the stored program where it was declared.

First Introduced In

MySQL 5.5 (2010) – following the SQL/PSM standard

Frequently Asked Questions

What error codes can I bind to a condition?

Bind any valid five-character SQLSTATE literal or any MySQL error number between 1000 and 4999.

Where must I place DECLARE CONDITION in a procedure?

Put it at the top of the BEGIN block, before executable SQL statements or control flow constructs.

Does a condition alias propagate to nested procedures?

No. The alias is local to the block where it is declared.

How do I remove a condition alias?

You cannot drop it explicitly. It disappears automatically when the block finishes executing.