MySQL - DECLARE ... CONDITION Statement

While working with stored procedures in MySQL if an exception or occurs the execution of the procedure terminates abruptly, to avoid this you need to handle the exceptions in MYSQL.

MySQL provides a handler to handle the exceptions in the stored procedures. You can handle these exceptions by declaring a handler using the MySQL DECLARE ... HANDLER Statement.

While declaring a handler you need to provide a handler action (CONTINUE or, EXIT or, UNDO), condition value and statements.

DECLARE ... CONDITION Statement

Using the DECLARE ... CONDITION statement you can associate a name with the required error condition. Later you can use this in the DECLARE ... HANDLER statement.

Syntax

Following is the syntax od the DECLARE ... CONDITION Statement −

DECLARE condition_name CONDITION FOR condition_value

Where, condition_name is the name to be associated with the condition and condition_value is the specific condition or class of conditions. The condition value can be

• An integer value representing the MYSQL error code.
• A 5-character string literal representing the SQLSTATE value.

Example

Following procedure accepts two number and perform a division operation if the divisor Is 0 this prints an error. In Here we have defined our own handler condition to display the error.

DELIMITER //
   CREATE PROCEDURE sample(IN dividend INT, IN divisor INT, OUT res double)
   BEGIN
      DECLARE dividedByZero CONDITION FOR SQLSTATE '22012';
      DECLARE CONTINUE HANDLER FOR dividedByZero
      RESIGNAL SET MESSAGE_TEXT = 'Error Denominator given is zero';
   IF divisor = 0 THEN
      SIGNAL dividedByZero;
   ELSE
      SET res:= dividend / divisor;
   END IF;
   END//
DELIMITER ;

If you call the above procedure by passing the second parameter as '0' as −

CALL sample(25, 0, @res);

The following error will be displayed −

ERROR 1644 (22012): Error Denominator given is zero