How can we write MySQL handler, in a stored procedure, that sets the particular value of a variable and continues the execution?


As we know that whenever an exception occurred in MySQL stored procedure, it is very important to handle it by throwing proper error message because if we do not handle the exception, there would be a chance to fail application with that certain exception in a stored procedure. MySQL provides a handler that sets the particular value of a variable and continues the execution. To demonstrate it, we are using the following example in which we are trying to insert a duplicate value in a Primary key column.

mysql> DELIMITER //
mysql> Create Procedure Insert_Studentdetails2(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20),OUT got_error INT)
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error=1;
    -> INSERT INTO Student_detail
    -> (Studentid, StudentName, Address)
    -> Values(S_Studentid,S_StudentName,S_Address);
    -> Select * from Student_detail;
    -> END //
Query OK, 0 rows affected (0.00 sec)
mysql> Delimiter ;

mysql> CALL Insert_Studentdetails2(104,'Ram',‘Chandigarh',@got_error);
+-----------+-------------+------------+
| Studentid | StudentName | address    |
+-----------+-------------+------------+
| 100       | Gaurav      | Delhi      |
| 101       | Raman       | Shimla     |
| 103       | Rahul       | Jaipur     |
| 104       | Ram         | Chandigarh |
+-----------+-------------+------------+
4 rows in set (0.04 sec)
Query OK, 0 rows affected (0.06 sec)

Now, if we will try to add any duplicate value of column ‘studentid’ then it will continue the execution, it gives the result set of the query written in procedure ‘select * from student_detail’ and sets the value of the got_error variable to 1.

mysql> CALL Insert_Studentdetails2(104,'Shyam','Hisar',@got_error);
+-----------+-------------+------------+
| Studentid | StudentName | address    |
+-----------+-------------+------------+
| 100       | Gaurav      | Delhi      |
| 101       | Raman       | Shimla     |
| 103       | Rahul       | Jaipur     |
| 104       | Ram         | Chandigarh |
+-----------+-------------+------------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.03 sec)

mysql> Select @got_error;
+------------+
| @got_error |
+------------+
| 1          |
+------------+
1 row in set (0.00 sec)

Updated on: 12-Feb-2020

149 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements