MySQL DECLARE … CONDITION Statement

Summary: in this tutorial, you will learn how to use MySQL DECLARE ... CONDITION statement to declare a named error condition.

Introduction to MySQL DECLARE … CONDITION statement

A condition is a warning or error that occurs within a stored procedure. MySQL uses an error code or an SQLSTATE value to represent a condition. However, the error code or SQLSTATE may not be clear.

To address this issue, MySQL provides the DECLARE ... CONDITION statement to declare a named error condition that associates a name with a condition.

Here’s the syntax of the DECLARE ... CONDITION statement:

DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the condition’s name after the DECLARE keyword (codition_name).
• Second, provide the condition value (condition_value) after the FOR keyword. The condition value can be a MySQL error code or a SQLSTATE value.

Note that you should not use MySQL error code 0 or SQLSTATE value that begins with '00' because these indicate success rather than a warning or an error condition.

The named conditions make your store procedures code clearer and easier to maintain.

For example, the following declares a HANDLER for the MySQL error code 1051:

DECLARE CONTINUE HANDLER FOR 1051
  BEGIN
    -- body of handler
  END;Code language: SQL (Structured Query Language) (sql)

But 1015 doesn’t tell its meaning explicitly. To understand it, you need to look it up and find out its meaning, which is “unknown table”.

By using a named condition, the code expresses its intent very clearly. For example:

DECLARE unknown_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR unknown_table 
  BEGIN
    -- body of handler
  END;Code language: SQL (Structured Query Language) (sql)

MySQL DECLARE…CONDITION example

The following example shows how to use the DECLARE ... CONDITION to create a stored procedure that retrieves data from a table specified by the tbl_name parameter:

DELIMITER $$

CREATE PROCEDURE GetData(
	IN tbl_name VARCHAR(255)
)
BEGIN
    DECLARE unknown_table CONDITION FOR 1051;
    
    DECLARE EXIT HANDLER FOR unknown_table 
	BEGIN
		SHOW ERRORS;
    END;
    
    SET @sql_query = CONCAT('SELECT * FROM ', tbl_name);
    
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

How it works.

First, declare a named condition unknown_table that is associated with the MySQL error code 1051:

DECLARE unknown_table CONDITION FOR 1051;Code language: SQL (Structured Query Language) (sql)

Second, declare an exit handler that uses the named condition unknown_table and show the errors if the procedure attempts to select from a table that does not exist:

DECLARE EXIT HANDLER FOR unknown_table 
   BEGIN
      SHOW ERRORS;
   END;Code language: SQL (Structured Query Language) (sql)

Third, construct a prepared statement that retrieves all rows and columns from the table specified by the tbl_name parameter:

 SET @sql_query = CONCAT('SELECT * FROM ', tbl_name);
    
 PREPARE stmt FROM @sql_query;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;Code language: SQL (Structured Query Language) (sql)

The following example uses the GetData() stored procedure to retrieve data from the employees table in the sample database:

CALL GetData('employees');Code language: SQL (Structured Query Language) (sql)

It returns all rows and columns of employees table.

However, if we call the stored procedure that retrieves data from the abc table that doesn’t exist, the stored procedure returns an error:

CALL GetData('abc');Code language: SQL (Structured Query Language) (sql)

Error:

+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Error | 1146 | Table 'classicmodels.abc' doesn't exist |
+-------+------+-----------------------------------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use MySQL DECLARE ... CONDITION to associate a name with a condition specified by a MySQL error code or SQLSTATE value to make the stored procedure code more readable and expressive.