This tutorial shows you how to use MySQL handler to handle exceptions or errors encountered in stored procedures.

When an error occurs inside a stored procedure,it is important to handle it appropriately,such as continuing or exiting the current code block’s execution,and issuing a meaningful error message.

MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g.,specific error codes.

Declaring a handler

To declare a handler,you use theDECLARE HANDLERstatement as follows:

If a condition whose value matches thecondition_value,MySQL will execute thestatementand continue or exit the current code block based on theaction.

Theactionaccepts one of the following values:

• CONTINUE: the execution of the enclosing code block (BEGIN…END) continues.
• EXIT: the execution of the enclosing code block,where the handler is declared,terminates.

Thecondition_valuespecifies a particular condition or a class of conditions that activates the handler. Thecondition_valueaccepts one of the following values:

• A MySQL error code.
• A standardSQLSTATEvalue. Or it can be anSQLWARNING,NOTFOUNDorSQLEXCEPTIONcondition,which is shorthand for the class ofSQLSTATEvalues. TheNOTFOUNDcondition is used for aorSELECT INTO variable_liststatement.
• A named condition associated with either a MySQL error code orSQLSTATEvalue.

Thestatementcould be a simple statement or a compound statement enclosing by theBEGINandENDkeywords.

MySQL error handling examples

Let’s look into several examples of declaring handlers.

The following handler means if an error occurs,set the value of thehas_errorvariable to 1 and continue the execution.

</td>
<td class=”crayon-code”>
<div class=”crayon-pre”>
<div id=”crayon-548161c73f05d207284107-1″ class=”crayon-line”><span class=”crayon-r”>DECLARE<span class=”crayon-h”> <span class=”crayon-st”>CONTINUE<span class=”crayon-h”> <span class=”crayon-e”>HANDLER <span class=”crayon-st”>FOR<span class=”crayon-h”> <span class=”crayon-e”>SQLEXCEPTION <span class=”crayon-e”>SET <span class=”crayon-v”>has_error<span class=”crayon-h”> <span class=”crayon-o”>=<span class=”crayon-h”> <span class=”crayon-cn”>1<span class=”crayon-sy”>;

</td>

</tr>

</table>

The following is another handler; it means that in case any error occurs,rollback the previous operation,issue an error message and exit the current code block. If you declare it inside theBEGIN ENDblock of a stored procedure,it will terminate stored procedure immediately.

</td>
<td class=”crayon-code”>
<div class=”crayon-pre”>
<div id=”crayon-548161c73f066533478329-1″ class=”crayon-line”><span class=”crayon-r”>DECLARE<span class=”crayon-h”> <span class=”crayon-e”>EXIT <span class=”crayon-e”>HANDLER <span class=”crayon-st”>FOR<span class=”crayon-h”> <span class=”crayon-e”>SQLEXCEPTION
<div id=”crayon-548161c73f066533478329-2″ class=”crayon-line crayon-striped-line”><span class=”crayon-e”>BEGIN
<div id=”crayon-548161c73f066533478329-3″ class=”crayon-line”><span class=”crayon-v”>ROLLBACK<span class=”crayon-sy”>;
<div id=”crayon-548161c73f066533478329-4″ class=”crayon-line crayon-striped-line”><span class=”crayon-i”>SELECT<span class=”crayon-h”> <span class=”crayon-s”>’An error has occurred,operation rollbacked and the stored procedure was terminated'<span class=”crayon-sy”>;
<div id=”crayon-548161c73f066533478329-5″ class=”crayon-line”><span class=”crayon-st”>END<span class=”crayon-sy”>;

</td>

</tr>

</table>

If there are no more rows to fetch,in case of aorstatement,set the value of theno_row_foundvariable to 1 and continue execution.

</td>
<td class=”crayon-code”>
<div class=”crayon-pre”>
<div id=”crayon-548161c73f06e998259390-1″ class=”crayon-line”><span class=”crayon-r”>DECLARE<span class=”crayon-h”> <span class=”crayon-st”>CONTINUE<span class=”crayon-h”> <span class=”crayon-e”>HANDLER <span class=”crayon-st”>FOR<span class=”crayon-h”> <span class=”crayon-st”>NOT<span class=”crayon-h”> <span class=”crayon-e”>FOUND <span class=”crayon-e”>SET <span class=”crayon-v”>no_row_found<span class=”crayon-h”> <span class=”crayon-o”>=<span class=”crayon-h”> <span class=”crayon-cn”>1<span class=”crayon-sy”>;

</td>

</tr>

</table>

If a duplicate key error occurs,MySQL error 1062 is issued. The following handler issues an error message and continues execution.

</td>
<td class=”crayon-code”>
<div class=”crayon-pre”>
<div id=”crayon-548161c73f075760806018-1″ class=”crayon-line”><span class=”crayon-r”>DECLARE<span class=”crayon-h”> <span class=”crayon-st”>CONTINUE<span class=”crayon-h”> <span class=”crayon-e”>HANDLER <span class=”crayon-st”>FOR<span class=”crayon-h”> <span class=”crayon-cn”>1062
<div id=”crayon-548161c73f075760806018-2″ class=”crayon-line crayon-striped-line”><span class=”crayon-i”>SELECT<span class=”crayon-h”> <span class=”crayon-s”>’Error,duplicate key occurred'<span class=”crayon-sy”>;

</td>

</tr>

</table>

MySQL handler example in stored procedures

First,we create a new table namedarticle_tagsfor the demonstration:

</td>
<td class=”crayon-code”>
<div class=”crayon-pre”>
<div id=”crayon-548161c73f07c185701323-1″ class=”crayon-line”><span class=”crayon-e”>CREATE <span class=”crayon-e”>TABLE <span class=”crayon-e”>article_tags<span class=”crayon-sy”>(
<div id=”crayon-548161c73f07c185701323-2″ class=”crayon-line crayon-striped-line”><span class=”crayon-h”><span class=”crayon-e”>article_id <span class=”crayon-t”>INT<span class=”crayon-sy”>,
<div id=”crayon-548161c73f07c185701323-3″ class=”crayon-line”><span class=”crayon-h”><span class=”crayon-e”>tag_id <span class=”crayon-t”>INT<span class=”crayon-sy”>,
<div id=”crayon-548161c73f07c185701323-4″ class=”crayon-line crayon-striped-line”><span class=”crayon-h”><span class=”crayon-e”>PRIMARY <span class=”crayon-e”>KEY<span class=”crayon-sy”>(<span class=”crayon-v”>article_id<span class=”crayon-sy”>,<span class=”crayon-v”>tag_id<span class=”crayon-sy”>)
<div id=”crayon-548161c73f07c185701323-5″ class=”crayon-line”><span class=”crayon-sy”>)<span class=”crayon-sy”>;

</td>

</tr>

</table>

Thearticle_tagstable stores the relationships between articles and tags. Each article may have many tags and vice versa. For the sake of simplicity,we don’t createarticlesandtagstables,as well as thein thearticle_tagstable.