How can we write MySQL handler, in a stored procedure, that throws an error message and exit 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 throws an error message and exits 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_Studentdetails3(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20))
    -> BEGIN
    -> 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_Studentdetails3(105, 'Mohan', 'Chandigarh');
| Studentid | StudentName | address    |
|       100 | Gaurav      | Delhi      |
|       101 | Raman       | Shimla     |
|       103 | Rahul       | Jaipur     |
|       104 | Ram         | Chandigarh |
|       105 | Mohan       | Chandigarh |
5 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 exit the execution, it did not give the result set of the query written in procedure ‘select * from student_detail’ and give only the error message ‘got an error’.

mysql> CALL Insert_Studentdetails3(105, 'Sohan', 'Bhopal');
| Got an error |
| Got an error |
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Updated on: 22-Jun-2020


Kickstart Your Career

Get certified by completing the course

Get Started