When an error is found during execution of a stored procedure, it can be handled in MySQL in two ways. The current block may continue to execute by throwing a warning or exit from executing by issuing a meaningful message. The error handlers can handle warnings, exceptions, specific error codes... etc. The syntax for error handling in store procedures is:

Syntax

DECLARE handlerAction HANDLER

  FOR conditionValue [, conditionValue]...

  Statement;

Where:

  • handlerAction can be CONTINUE or EXIT or UNDO. It defines the action to be taken when an error occurs.
  • conditionValue is mysql_error_code or SQLSTATE [VALUE], sqlstate_value or condition_name or SQLWARNING or NOT FOUND or SQLEXCEPTION. When any of these occurs it executes the "statement" block.
  • The statement block can be a simple statement or multiple statements with BEGIN and END keywords.

The following examples give usage of CONTINUE and EXIT handlers:

DECLARE CONTINUE HANDLER FOR SQLWARNING

   BEGIN

      SET done = TRUE;

   END;

 

DECLARE EXIT HANDLER FOR SQLEXCEPTION

   BEGIN

      ROLLBACK;

      SELECT 'An error occurred';

   END;

Consider a stored procedure which creates a database table as 'tblstudents' with 'rollNumber' and 'names' as columns. For the first time the procedure runs without an error. Subsequent times when the procedure is executed, it throws Error: 1065 SQLSTATE: 42000 (ER_NONUNIQ_TABLE) Message: Not unique table/alias: '%s'.

When the handler action is CONTINUE, the error is hit and the statements for error handler is executed. The procedure continues to execute till the end. However, if the handler action is EXIT, the statement for error handler is executed and procedure terminates.

We can see two different outputs of same procedure executed with CONTINUE and EXIT handlers:

CONTINUE

DELIMITER $$;

CREATE procedure createtable()

BEGIN

   DECLARE notUniqueTable CONDITION FOR SQLSTATE '42000';

   DECLARE CONTINUE HANDLER FOR notUniqueTable

     SELECT 'The table already exists';

   CREATE TABLE tblstudents (

      roll int(11) NOT NULL AUTO_INCREMENT,

      name varchar(45) DEFAULT NULL,

      PRIMARY KEY (id)

   );

   SELECT 'Procedure executing after error';

END; $$

CALL createtable();

Results of the CONTINUE handler are:

  • Execution results of routine 'createtable'.
  • The table already exists.
  • Procedure executing after error.

EXIT

DELIMITER $$;

CREATE procedure createtable()

BEGIN

   DECLARE notUniqueTable CONDITION FOR SQLSTATE '42000';

   DECLARE EXIT HANDLER FOR notUniqueTable

      SELECT 'The table already exists';

   CREATE TABLE tblstudents (

      roll int(11) NOT NULL AUTO_INCREMENT,

      name varchar(45) DEFAULT NULL,

      PRIMARY KEY (id)

   );

   SELECT 'Procedure executing after error';

END; $$

CALL createtable();

Results of the EXIT handler are:

  • Execution results of routine 'createtable'.
  • The table already exists.
  • The SELECT statement after CREATE TABLE are not executed.