How to capture exceptions in MySQL stored procedures

Use of the DECLARE handler: DECLAREhandler_typeHANDLERFORcondition_value [,...] sp_statement where the VALUE of handler_type is: CONTINUE | EXIT | UNDOcondition_value. VALUE range: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT

Use of the declare handler: DECLARE handler_type handler for condition_value [,...] sp_statement where the VALUE of handler_type is: CONTINUE | EXIT | UNDO condition_value. VALUE range: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT

Use of the DECLARE handler:

DECLARE handler_type handler for condition_value [,...] sp_statement

Where,

Value range of handler_type: CONTINUE | EXIT | UNDO

VALUE range of condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | not found | SQLEXCEPTION | mysql_error_code

This statement specifies each handler that can process one or more conditions. If one or more conditions are generated, the specified statement is executed. For a CONTINUE processing program, the execution of the current subroutine continues after the execution of the Processing Program Statement. For the EXIT handler, the execution of the current BEGIN... END compound statement is terminated. UNDO handler type statements are not supported yet.

· SQLWARNING is a shorthand for all SQLSTATE codes starting with 01.

· Not found is a shorthand for all SQLSTATE codes starting with 02.

· SQLEXCEPTION is a shorthand for all SQLSTATE Codes NOT captured by SQLWARNING or not found.

Note: apart from the SQLSTATE value, the MySQL error code is not supported.

Example:

delimiter $$CREATE TABLE `_t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `val1` varchar(20) DEFAULT NULL,  `val2` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1$$

DELIMITER $ create definer = 'abandonship '@' % 'PROCEDURE 'P _ testexception' () BEGIN declare _ var, _ err int default 0; declare continue handler for sqlexception, sqlwarning, not found set _ err = 1; insert into _ t1 (val1, val2) value (2012, 'abandonship '); if _ err = 1 then set _ var = 2; end if; select case when _ var = 2 then' error 'else _ var end; END

An error occurred while calling the stored procedure.