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 :

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of