Error Handling in MySQL Stored Procedure

By /
Error Handling In Stored Procedure

In this tutorial, we will learn how we can handle the errors effectively in the MySQL stored procedures. We will see multiple aspects and possibilities while creating the error conditions, handlers and handling those errors. So, let’s get started!

Introduction to MySQL Error Handling

It is very important to handle the errors in MySQL stored programs such as procedures, functions, triggers and events. If you do not handle the errors in a proper way, the end user will see some system-generated error message that is not understandable to everyone. Also, it is a good practice which helps programmers for debugging the programs as well.

In MySQL, we can easily create handlers that can handle the warnings, exceptions and errors.

In simple terms, the handler is a statement that handles the error when a specific condition meets.

Now let’s see the syntax to define a handler.

Declare an Error Handler

We can declare an error handler using the DECLARE … HANDLER statement.

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}Code language: SQL (Structured Query Language) (sql)

Here, you can specify one of the three handler actions which having meaning as of the following-

  • CONTINUE- The present program is still being run.
  • EXIT – The BEGIN… END compound statement in which the handler is declared ends execution. Even if the condition occurs in an inner block, this is still true.
  • UNDO – MySQL does not support it.

A specific condition or a group of circumstances that activate the handler are specified by the condition value. One of the following values may be used with the condition value:

  • mysql_error_code – A MySQL error code represented by an integer literal, such as 1051 for “unknown table”:
  • SQLSTATE sqlstate_value – a literal 5-character string specifying an SQLSTATE value, such as ’42S01′ to denote ‘unknown table’:
  • condition_name – previously defined condition name using DECLARE… CONDITION. A condition name may be connected to an SQLSTATE value or a MySQL error number.
  • NOT FOUND –
  • SQLEXCEPTION – abbreviation for the group of SQLSTATE values that start with the number “02”. It is used to control what occurs when a cursor reaches the end of a data collection, making it significant in the context of cursors.

We have covered most of the theory parts about error handling. Let’s now take some examples to demonstrate how error handling is actually done.

Stored Procedure Error Handling Examples

Here, we will create a table and write a stored procedure to insert data into it. If the record is already present, we will issue an error message.

Let’s create a table for demonstration.

CREATE TABLE emps(
id INT PRIMARY KEY,
dept VARCHAR(10),
city VARCHAR(20)
);Code language: SQL (Structured Query Language) (sql)

Creating a stored procedure to insert data

Now, we will create a stored procedure to insert the data into the table.

Inside the stored procedure, we will create an exit error handler for the error code 1062 which is for duplicate entries in the table. If the handler is invoked, we will show the message that a duplicate entry has been made and the exit handler will terminate the stored procedure execution.

DROP PROCEDURE IF EXISTS insertEmps;
DELIMITER //
CREATE PROCEDURE insertEmps(IN empId INT, IN dept VARCHAR(10), IN city VARCHAR(20))
BEGIN
DECLARE EXIT HANDLER FOR 1062
BEGIN
SELECT CONCAT("duplicate entry for the emp id ",empId) AS errorMessage;
END;
INSERT INTO emps VALUES(empId,dept,city);
SELECT * FROM emps WHERE id = empId;
END 
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Here, we take three parameters which are employee id, department id and city.

Writing an exit handler for error code 1062

Then we write an exit handler for the error code 1062. When it gets invoked, the message will be shown and the procedure terminates.

If the employee id is unique then the insert statement will get executed and the data that is inserted will be displayed as it is.

Note that, if the error handler is invoked, the SELECT * FROM statement at the end will not execute because as soon as the handler is activated, the message will be displayed and it terminates the procedure.

Let’s call the stored procedure now.

CALL insertEmps(101,"IT","Mumbai");Code language: SQL (Structured Query Language) (sql)
Call Stored Procedure
Call Stored Procedure

As you can see, the data that we have inserted into the table is shown in the output.

Inserting duplicate data

Let’s insert the same data that we have inserted just now. It should show the message that we defined in the handler.

CALL insertEmps(101,"IT","Mumbai");Code language: SQL (Structured Query Language) (sql)
Duplicate Entry
Duplicate Entry

As you can see here, we got the error message.

Switching the exiting handler

Now, let’s change the error handler from EXIT to CONTINUE and see the change.

DROP PROCEDURE IF EXISTS insertEmps;
DELIMITER //
CREATE PROCEDURE insertEmps(IN empId INT, IN dept VARCHAR(10), IN city VARCHAR(20))
BEGIN
DECLARE CONTINUE HANDLER FOR 1062
BEGIN
SELECT CONCAT("duplicate entry for the emp id ",empId) AS errorMessage;
END;
INSERT INTO emps VALUES(empId,dept,city);
SELECT * FROM emps WHERE id = empId;
END 
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)
CALL insertEmps(101,"IT","Mumbai");Code language: SQL (Structured Query Language) (sql)
Duplicate Entry With Continue Handler
Duplicate Entry With Continue Handler

As you can see, we first got the error message and then the record which is having the given employee id is displayed.

MySQL Error Handler Precedence

There might be possibilities that you have multiple handlers for the same error.

If you have several handlers for the same error, MySQL will call the one that can handle it the most specifically first based on the guidelines below:

  • Because a MySQL error code is the most specific, it always corresponds to an error.
  • An SQLSTATE is less precise since it can map to several MySQL error codes.
  • It is the most general sense, an SQLEXCPETION or SQLWARNING is the abbreviation for a group of SQLSTATES data.

For example, let’s take the previously created stored procedure example.

We will slightly modify that procedure and add two more handlers for the same error.

So, the new stored procedure will look like this-

DROP PROCEDURE IF EXISTS insertEmps;
DELIMITER //
CREATE PROCEDURE insertEmps(IN empId INT, IN dept VARCHAR(10), IN city VARCHAR(20))
BEGIN
DECLARE EXIT HANDLER FOR 1062 SELECT CONCAT("duplicate entry for the emp id ",empId) AS errorMessage;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT "SQLEXCEPTION occured" AS errorMessage;
DECLARE EXIT HANDLER FOR SQLSTATE "23000" SELECT "SQLSTATE 23000" AS errorMessage;
INSERT INTO emps VALUES(empId,dept,city);
SELECT * FROM emps WHERE id = empId;
END 
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Now let’s try inserting the duplicate data.

CALL insertEmps(101,"IT","Mumbai");Code language: SQL (Structured Query Language) (sql)
Error Handler Precedance Example
Error Handler Precedence Example

As you can see, even if we have three handlers for the same error, the one which is specified with the error code is encountered.

Summary

In this tutorial, we have learned how to handle errors in MySQL stored procedures. This topic is very vast but I have tried to make it as simple as possible without adding unnecessary things which you can learn later yourself. I hope you find this tutorial helpful. If you do, don’t forget to share it with your friends.

References