Error Handling in MySQL

What is Error Handling in MySQL?

Error Handling in MySQL is mainly in stored procedures if any error occurs while executing the stored procedure code. In MySQL, we can define handlers that handle general errors or other conditions such as warnings or exceptions to specific conditions.

Students table

We will create a stored procedure that will accept the values for roll number, name, marks, class, and gender for students and insert a record in the students’ table like below. Our students’ table has roll_num as the primary key, so if we try to add the same key then we will get the exception. In the following stored procedure, if we get any exception while adding, that will be handled in the exception block and the respective message will be returned.

Create Statement for SP:

DELIMITER $$
CREATE PROCEDURE `save_students`(
      IN in_roll_no INTEGER,
      IN in_name varchar(45),
      IN in_marks INTEGER,
      IN in_class integer,
      IN in_gender varchar(1)
)
BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
            ROLLBACK; — This will Rollback the sql transaction if any changes are done
            SELECT concat(‘Duplicate roll_number: ‘, in_roll_no) as message;
      END;

      INSERT INTO students VALUES(in_roll_no, in_name, in_marks, in_class, in_gender);
      SELECT ‘success’ as message, std.* FROM students std WHERE roll_num = in_roll_no;
END$$
DELIMITER ;

To call the Stored procedure we will use the below statements.

call save_students(8, ‘abcd’, 200, 3, ‘F’);

For the above roll number, since we already have data present for roll number 8, we will get the exception while inserting it into the students’ table, so when we are trying to run the above query we will get the following result.

To get a successful response, we will add the new roll number with students’ data.

call save_students(9, ‘Anna’, 190, 6, ‘F’);

We will get the following result.

If we want to get an actual exception message we can update the stored procedure like below.

Code for Exception message:

GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
ROLLBACK; — This will Rollback the sql transaction if any changes are done
SELECT concat(‘SQL Exception is: ‘, @p1, ‘, ‘ , @p2) as message;

For the above-updated code if we try to insert a duplicate roll number we will get the following response.

How to throw exceptions in the Stored Procedure?

We have seen IF… ELSE in MySQL, we will create one stored procedure for performing the division of two numbers and using IF… ELSE we will check if the value for the divisor is zero, if the value of the divisor is zero then we will throw the exception else we will return the result as shown in the below code.

Create Statement for SP:

DELIMITER $$
CREATE PROCEDURE `perform_division`(
      IN in_dividend INTEGER,
      IN in_divisor INTEGER
)
BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
            GET DIAGNOSTICS CONDITION 1
            @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
            ROLLBACK; — This will Rollback the sql transaction if any changes are done
            SELECT concat(‘SQL Exception is: ‘, @p1, ‘, ‘ , @p2) as result;
      END;
      IF ( 0 = in_divisor ) THEN
            BEGIN
                  SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘value for divisor should not be zero’;
            END;
      ELSE
            BEGIN
                  SELECT in_dividend / in_divisor as result;
            END;
      END IF;
END$$
DELIMITER ;