Error Handling in MySQL Stored Procedures

It is very important to handle the Exceptions in stored procedures by exiting the code by providing a proper error message or by exiting or continuing the execution. We can define handlers in MySQL to handle the error causing conditions.

How To Declare a handler

The syntax to write a handler is:

DECLARE action HANDLER FOR condition_value statement;

 In the above syntax, MySQL will perform the action if the condition matches the condition_value. Here, action may contain one of the following values:

• Continue: To continue the enclosing code block (BEGIN...END)
• Exit: To terminate the execution of the block

condition_value activates the handler on specific condition or class of conditions. It accepts one of the following values:

• MySQL error code
• Standard SQLSTATE value or shorthands of SQLSTATE like SQLWARNING, NOTFOUND or SQLEXCEPTION.
• The naming condition associated with the MySQL error code or sqlstate value.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasError = 1;

In the above code, if an SQLEXCEPTION occur, the handler will set the value of hasError to 1.

Rollback on an Error

Here is an example of rollback previous operations and genarate an error message with the code termination on sql exception.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'Sql Exception caused rollback and operation termination';
END

Implementation of Handler in Stored Procedure

MySQL handler example in stored procedure 

CREATE PROCEDURE AddStudentDetails(
    S_Studentid INT, S_StudentName Varchar(20)
)
BEGIN
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
 	SELECT CONCAT('Duplicate key ('inStudentId,',',inStudentName') occurred') AS message;
    END;
    
    INSERT INTO Student(S_Studentid,S_StudentName)
    VALUES(Studentid, StudentName);
    
    SELECT COUNT(*) 
    FROM Student
    WHERE S_Studentid = Studentid;
    
END$$

DELIMITER ;

In this example, error code 1062 signifies duplicate key. Duplicate values in key will activate the handler that willl generate the error message and terminate the code block. Otherwise, if key is unique, it will insert the values in Student table.

If we use multiple handlers, MySQL will call the most specific handler to handle the errors which involves priority.