This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the full demonstration on how to manage error/exception handling in the Stored Procedure of MySQL.
Whenever an exception is occurring in a stored procedure, it is very important to handle it by showing proper error messages.
If you do not handle the exception, there would be a chance to fail application with the certain exception in a stored procedure.
If you get an error in stored procedure, instead of an exit, you should continue without any error message. That means you can show any default or custom error code or message to the application/user.
MySQL provides Handler to handle the exception in the stored procedure.
Below is a full demonstration of a handler with examples:
|
1 2 3 4 5 6 7 8 9 10 |
/*Create Employee database for demo */ CREATE DATABASE Employee; /*Create sample EmployeeDetails table.*/ CREATE TABLE Employee.tbl_EmployeeDetails ( EmpID INTEGER ,EmpName VARCHAR(50) ,EmailAddress VARCHAR(50) ,CONSTRAINT pk_tbl_EmployeeDetails_EmpID PRIMARY KEY (EmpID) )ENGINE = InnoDB; |
How to declare handler in store procedure:
Syntax of Handler:
|
1 |
DECLARE handler_action HANDLER FOR condition_value ... statement |
Three type of Handler_Action:
- CONTINUE
- EXIT
- UNDO
Type of Condition Value:
- mysql_error_code
- sqlstate_value
- SQLWarning
- SQLException
- NotFound
How to write handler in stored procedure?
E.g.
|
1 2 3 4 |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'Error occured'; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET IsError=1; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET IsError=1; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET IsError = 1; |
The Above are four different handler examples. Now, I am going to insert a duplicate value into EmpID column.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DELIMITER // CREATE PROCEDURE Employee.usp_InsertEmployeeDetails ( InputEmpID INTEGER ,InputEmpName VARCHAR(50) ,InputEmailAddress VARCHAR(50) ) /**************************************************************** Authors Name : Anvesh Patel Created Date : 2015-05-20 Description : This is demo stored procedure to insert record into table with proper error handling.Basically for www.dbrnd.com readers. *****************************************************************/ BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'Error occured'; INSERT INTO Employee.tbl_EmployeeDetails ( EmpID ,EmpName ,EmailAddress ) VALUES ( InputEmpID ,InputEmpName ,InputEmailAddress ); SELECT *FROM Employee.tbl_EmployeeDetails; END // DELIMITER ; |
In the above SP, I defined a CONTINUE handler with my custom exception message.
Now, call the above SP two times with same EmpID.
The first time, it will execute successfully, but the second time it will throw a custom error message.
As we defined CONTINUE handler, so it will just show an error message and CONTINUE to next part of the SELECT statement.
Let’s See,
|
1 2 |
CALL Employee.usp_InsertEmployeeDetails (1,'Anvesh','anvesh@gmail.com'); CALL Employee.usp_InsertEmployeeDetails (1,'Roy','Roy@gmail.com'); |
Above are the two different calls with same EmpID value. The first call executes without any error message and the second call execute with an error message.
The resule of Second Call:
As we defined CONTINUE, so you can find two results in above image. One is our custom error message and second is the result of the defined SELECT statement.
The execution didn’t stop by error, and it continued for another part.
Now, check the EXIT handler:
Please modify your handler and replace CONTINUE by EXIT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DELIMITER // CREATE PROCEDURE Employee.usp_InsertEmployeeDetails ( InputEmpID INTEGER ,InputEmpName VARCHAR(50) ,InputEmailAddress VARCHAR(50) ) /***************************************************************** Authors Name : Anvesh Patel Created Date : 2015-05-20 Description : This is demo stored procedure to insert record into table with proper error handling.Basically for www.dbrnd.com readers. ******************************************************************/ BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'Error occured'; INSERT INTO Employee.tbl_EmployeeDetails ( EmpID ,EmpName ,EmailAddress ) VALUES ( InputEmpID ,InputEmpName ,InputEmailAddress ); SELECT *FROM Employee.tbl_EmployeeDetails; END // DELIMITER ; |
Call with the same parameter:
|
1 |
CALL Employee.usp_InsertEmployeeDetails (1,'Roy','Roy@gmail.com'); |
The Result is an only error message, and you cannot find two results as we defined EXIT to exit the code when an error occurred.
The best practice is to create a 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 a stored procedure for this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
DELIMITER // CREATE PROCEDURE Employee.usp_InsertEmployeeDetails ( InputEmpID INTEGER ,InputEmpName VARCHAR(50) ,InputEmailAddress VARCHAR(50) ,out IsError INTEGER ) /*********************************************************** Authors Name : Anvesh Patel Created Date : 2015-05-20 Description : This is demo stored procedure to insert record into table with proper error handling. Basically for www.dbrnd.com readers. ************************************************************/ BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET IsError=1; INSERT INTO Employee.tbl_EmployeeDetails ( EmpID ,EmpName ,EmailAddress ) VALUES ( InputEmpID ,InputEmpName ,InputEmailAddress ); SELECT *FROM Employee.tbl_EmployeeDetails; END // DELIMITER ; |
Now call the above SP and select output parameter:
|
1 2 |
CALL Employee.usp_InsertEmployeeDetails (1,'Roy','Roy@gmail.com',@IsError); SELECT @IsError; |
Now Results are:
Above is a simple demonstration of Error Handling in MySQL. You can also use SQLSTATE which shows default error messages of MySQL.
