MySQL Error Handling

In this post I will describe how to handle errors into mysql stored procedure.

Whenever any exception is occurring in a stored procedure, then this is very important to handle this exception by showing proper error messages.

If you do not handle the exception, then there would be chance to fail application with certain exception in stored procedure.

As per my advise, if you get an error in stored procedure, then instead of exit, you should continue without any error. Means you can show any default or custom error code or message to the application so base on this application can decide to show a proper message at user level.

MySQL provides Handler to handle exception in stored procedure.

Below is a full demonstration of handler with examples.

How to declare handler in store procedure:

Syntax of Handler:

Three type of Handler_Action:

  • EXIT
  • UNDO

Type of Condition Value:

  • mysql_error_code
  • sqlstate_value
  • SQLWarning
  • SQLException
  • NotFound

How to write handler in stored procedure ?:


The Above is four different handler example. Now I am going to insert duplicate value into EmpID column.

In this SP you can see I have defined one CONTINUE handler with my custom exception message. I have defined one insert statement and select statement.

Now call this SP two time with same EmpID.

This will execute first time successfully, but the second time will throw and custom error message.

As we defined CONTINUE handler so it will just show an error message and CONTINUE to return the result of the defined SELECT statement.

Let’s See,

During error SELECT is working or not.

Above are two different calls with same EmpID value. First call execute without any error message and second call execute with an error message.

Second Call Result Are:



As we defined CONTINUE so you found two result in above image one is our custom error message and second is result of defined select statement. The whole execution is not stopped by error and it continue at the end.

Now work with EXIT handler :

Please modify your handler and replace CONTINUE by EXIT.

Call with same parameter:

The Result is only error message, You cannot find two results as we defined EXIT mean to exit code when an error occurred.


Now best practice is to create one output parameter and store 1 if any error occurred.

Application code has to check this output parameter is NULL or 1.

1 = Error.

NULL = No Error.

Below is stored procedure for this.

Now call this SP to select output parameter.
Now Result are:



This is a simple demonstration of Error Handling in MySQL. You can also use SQLSTATE which shows default error messages of MySQL.


Other Related Articles :

Anvesh Patel

Leave a Reply

4 Comments on "MySQL Error Handling"

Notify of
Sort by:   newest | oldest | most voted

hi Avnesh i am struggling with DEFINER in stored procedure can you suggest some better idea
like DEFINER is =root@% then i am unable to execute this stored procedure from my local mysql db.

, advance thanks!

Shubhada Mulay

Hi Anvesh,
I am beginner to MYSQL. Could you please throw more light in details on this exception Handling Part? If you have any detailed version of this Please reply with the URL of that article as well.
Many Thanks in Advance.