Skip to main content

DECLARE ... CONDITION

The DECLARE ... CONDITION statement is used to declare an exception condition and assign a name to it, and associate the name with the handler specified by the exception condition.

The declaration of an exception condition must appear before the declaration of a cursor or handler. The syntax of the DECLARE ... CONDITION statement is as follows:

DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

The condition_name can be referenced in a later DECLARE ... HANDLER statement.

The condition_value specifies the condition or condition category associated with the exception condition name. It can take the following forms:

  • mysql_error_code: an integer literal that represents an error code. Note that code 0 indicates success, not an error, so do not use 0 as an error code.

  • SQLSTATE [VALUE] sqlstate_value: a 5-character string literal that represents an SQLSTATE value. Do not use SQLSTATE values that start with '00', because '00' values indicate success, not an error.

For more information about error codes and SQLSTATE values, see Common error codes of seekdb.

The exception condition name in a SIGNAL statement or in a RESIGNAL statement must be associated with an SQLSTATE value, not with an error code.

Using condition names can make stored program code clearer. Here is an example of an exception condition based on mysql_error_code:

DECLARE unknown_column CONDITION FOR 5217;
DECLARE CONTINUE HANDLER FOR unknown_column
  BEGIN
    -- Handler body
  END;

Here is an example of an exception condition based on an SQLSTATE value:

DECLARE unknown_column CONDITION FOR SQLSTATE '42S22';
DECLARE CONTINUE HANDLER FOR unknown_column
  BEGIN
    -- Handler body
  END;