Mysql Declare Procedure

 Mysql Declare Procedure is used to define a local variable within a procedure. The local variable are used within the procedure.

Understanding with Example

The Tutorial grasp you an example from 'Mysql Declare Procedure'. To understand this example we create a table 'Stu' and 'Lib' with required fieldname and datatype respectively.

Create Table Stu

Create Table Stu(Id int,Name Varchar(15));

Create Table Lib

Create Table Lib(Id int,libno int);

Insert Values into Stu

The Insert into is used to add the records or rows to the table 'Stu' and 'Lib'.

Insert Into Stu values(1,'Ajay');
Insert Into Stu values(2,'Bhanu');
Insert Into Stu values(4,'Rakesh');
Insert Into Stu values(5,'Santosh');
Insert Into Stu values(3,'Komal');

Insert Values into Lib

Insert Into Lib values(1,101);
Insert Into Lib values(2,102);
Insert Into Lib values(4,103);
Insert Into Lib values(5,104);
Insert Into Lib values(3,105);

Create Procedure Display

Now we create a procedure 'Display'.The Declare appear at the beginning of the stored procedure, followed by the first Begin Statement.The Default statement specify the name of table to be choosen when we don't specify any table.In this query we use 'stu' as default table and return the records from the table 'stu', when we don't choose any of the given table.

DELIMITER $$
CREATE PROCEDURE display()
BEGIN
    DECLARE tablename varchar(10) default 'stu';

    CASE tablename
	WHEN 'stu' THEN SELECT * FROM Stu;
	WHEN 'lib' THEN SELECT * FROM Lib;
    END CASE;
End$$

DELIMITER ;

Call Procedure Display

The call procedure Display return the records detail from table 'stud'.

call display ();

Result

+------+---------+
| Id   | Name    |
+------+---------+
| 1    | Ajay    |
| 2    | Bhanu   |
| 4    | Rakesh  |
| 5    | Santosh |
| 3    | Komal   |
+------+---------+