How can we write MySQL handler, in a stored procedure, that use SQLSTATE for default MySQL error 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 uses SQLSTATE for default MySQL error 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.

Example

mysql> Delimiter //
mysql> Create Procedure Insert_Studentdetails4(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20),OUT got_error INT)
   -> BEGIN
   -> DECLARE EXIT HANDLER FOR 1062 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)

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 query written in procedure ‘select * from student_detail’ and give only the default MySQL error message 1062 regarding entering duplicate values and also set the value of variable got_error to 1.

mysql> Delimiter ;
mysql> CALL Insert_Studentdetails4(104,'Ram','Chandigarh',@got_error);

Query OK, 0 rows affected (0.00 sec)

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