Suppose if we want to see all the records of a table by passing its name as the parameter of a stored procedure then following example will create a procedure named ‘details’ which accepts the name of the table as its parameter −mysql> DELIMITER // mysql> Create procedure details(tab_name Varchar(40))    -> BEGIN    -> SET @t:= CONCAT('Select * from', ' ', tab_name);    -> Prepare stmt FROM @t;    -> EXECUTE stmt;    -> END // Query OK, 0 rows affected (0.00 sec)Now invoke this procedure by giving the name of the table as its parameter and it will ... Read More

Following example will create a procedure named ‘tabledetails’ which gives all the details of a particular table stored in database.Examplemysql> DELIMITER // mysql> Create Procedure tabledetails()    -> BEGIN    -> DESCRIBE Student_detail;    -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> CALL tabledetails; +-------------+-------------+------+-----+---------+-------+ | Field       | Type        | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | Studentid   | int(11)     | NO   | PRI | NULL    |       | | StudentName | varchar(20) | YES  |     | NULL    |       | | address     | varchar(20) | YES  |     | NULL    |       | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) Query OK, 0 rows affected (0.04 sec)

Following are the two cases when MySQL IN() function returns NULL as result −Case-1 − When expression on  left side is NULL IN() function will return NULL if the expression on the left side is NULL. Following example will demonstrate it −mysql> Select NULL IN (1, 2, 3, 4, 10); +----------------------+ | NULL IN (1, 2, 3, 4, 10) | +----------------------+ |       NULL           | +----------------------+ 1 row in set (0.00 sec)Case-2 − When one of expression in the list is NULL and no match is foundIN() function will return NULL if it does ... Read More

If we have ALTER ROUTINE privileges for the procedure then with the help of DROP PROCEDURE statement we can drop a MySQL stored procedure. To demonstrate it, we are dropping a stored procedure named ‘coursedetails’ as follows −mysql> DROP PROCEDURE coursedetails; Query OK, 0 rows affected (0.68 sec)The above query will drop the procedure named ‘coursedetails’. It can be confirmed by running the statement SHOW CREATE PROCEDURE which will return an error because the procedure does not exist.

If we have ALTER ROUTINE privileges for the procedure then with the help of ALTER PROCEDURE statement we can alter a MySQL stored procedure. To demonstrate it we are taking an example of a stored procedure named ‘delete_studentinfo’ which have the following create a statement −mysql> SHOW CREATE PROCEDURE Delete_studentinfo\G *************************** 1. row ***************************            Procedure: Delete_studentinfo             sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION     Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_studentinfo`( IN p_id INT) BEGIN DELETE FROM student_info WHERE ID=p_id; END character_set_client: cp850 collation_connection: cp850_general_ci   Database Collation: ... Read More

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