- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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.
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)
- Related Articles
- How can we write MySQL handler, in a stored procedure, that throws an error message and exit the execution?
- How can we write MySQL handler, in a stored procedure, that throws an error message and continues the execution?
- How can we write MySQL handler in a stored procedure?
- How can we write MySQL handler, in a stored procedure, that sets the particular value of a variable and continues the execution?
- How to quit/ exit from MySQL stored procedure?
- How can we alter a MySQL stored procedure?
- How can we drop a MySQL stored procedure?
- How can we invoke MySQL stored procedure?
- What is stored procedure and how can we create MySQL stored procedures?
- How can a MySQL stored procedure call another MySQL stored procedure inside it?
- How to use FOR LOOP in MySQL Stored Procedure?
- How can we write MySQL stored procedure to select all the data from a table?
- How can we handle a result set inside MySQL stored procedure?
- How can we perform ROLLBACK transactions inside a MySQL stored procedure?
- How can we create MySQL stored procedure to calculate the factorial?