To demonstrate it we are creating a procedure named ‘selectdetails()’ which will fetch all the records from table ‘student_detail’.mysql> Delimiter // mysql> Create Procedure selectdetails()    -> BEGIN    -> Select * from student_detail;    -> END// Query OK, 0 rows affected (0.00 sec)Now, after invoking this procedure, we will get all the records from ‘student_detail’ table.mysql> Delimiter ; mysql> CALL selectdetails(); +-----------+-------------+------------+ | Studentid | StudentName | address    | +-----------+-------------+------------+ |       100 | Gaurav      | Delhi      | |       101 | Raman       | Shimla     | |       103 | Rahul       | Jaipur     | |       104 | Ram         | Chandigarh | |       105 | Mohan       | Chandigarh | +-----------+-------------+------------+ 5 rows in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

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.Examplemysql> Delimiter // mysql> Create Procedure Insert_Studentdetails4(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20), OUT got_error ... Read More

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.Examplemysql> Delimiter // mysql> Create Procedure Insert_Studentdetails3(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20))     -> BEGIN ... Read More

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 ... Read More

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 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.Examplemysql> DELIMITER // mysql> Create Procedure Insert_Studentdetails(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20))    -> BEGIN   ... Read More

Actually, MySQL has different comparison rules for quoted values such as strings and unquoted values such as numbers. On mixing the quoted and unquoted values in IN() function list may lead to the inconsistent result set. For example, we must not write the query with IN() function like below − Select Salary from employee where ID IN(1,’a’,2,3,’c’) Instead of this the better way to write the above query is as follows − Select Salary from employee where ID IN(‘1’,’a’,’2’,’3’,’c’)

In this scenario, we need to use the name of the column as ‘Expression’ which will then be compared with the values in the list. If a column has value/s matched within the list,  the output would be produced. For understanding it, consider the example from employee table having the following data −mysql> Select * from Employee; +----+--------+--------+ | ID | Name   | Salary | +----+--------+--------+ | 1  | Gaurav | 50000  | | 2  | Rahul  | 20000  | | 3  | Advik  | 25000  | | 4  | Aarav  | 65000  | | 5  | Ram    | 20000  | ... Read More

We can also use IN() function to compare row constructors. Consider the following example to make it clearer −mysql> Select (10,2) IN ((5,10),(10,2),(2,10),(100,100)); +--------------------------------------------+ | (10,2) IN ((5,10),(10,2),(2,10),(100,100)) | +--------------------------------------------+ | 1                                          | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> Select (10,2) IN ((5,10),(2,10),(100,100)); +-------------------------------------+ | (10,2) IN ((5,10),(2,10),(100,100)) | +-------------------------------------+ | 0                                   | +-------------------------------------+ 1 row in set (0.00 sec)

Whenever an exception occurred in MySQL stored procedure, it is very important to handle it by throwing a proper error message. Suppose, 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 to handle the exception in the stored procedure. Followings are the four kinds of MySQL handlers which can be used in a stored procedure −DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'got an error';The above handler will throw an error message and continues the execution.DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error=1;The above ... Read More

It is very important to handle the errors by throwing a proper error message. MySQL provides a handler to handle the error. We can declare a handler with the help of the following syntax −Syntax of handlerDECLARE handler_action FOR condition_value statement;The above syntax shows that we need to use DECLARE HANDLER statement to declare a handler. If a condition whose value matches the condition_value then MySQL will execute the statement and continue or exit the current code block based on the action. Followings are the three major things in the above syntax −Handler_action is of two types and can accept one ... Read More