Mysql Switch CASE

Mysql Switch Case perform one of several different actions based on several different conditions.

Understand with Example

The Section of Tutorial illustrate an example from 'Mysql Switch Case'.To understand an example from 'Mysql Switch Case' we simply create a table 'Stu' and 'Lib'.The table 'Stu' and 'Lib' is created 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 adds the records or rows into 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

The Create Procedure is used to create display that accept 'tablename'.The Procedure include a set of CASE that allows you to select the statement and return you the result on the basis of condition enclosed in BEGIN-END statement.

DELIMITER $$
CREATE PROCEDURE display(tablename VARCHAR(10))
BEGIN
      CASE tablename
	WHEN 'stu' THEN SELECT * FROM Stu;
	WHEN 'lib' THEN SELECT * FROM Lib;
    END CASE;
End$$

DELIMITER ;

Call Procedure Display

The call keyword is used to invoke the procedure display and return  the table 'lib' detail.  

call display ('lib');

Result

+------+-------+
| Id   | libno |
+------+-------+
| 1    | 101   |
| 2    | 102   |
| 4    | 103   |
| 5    | 104   |
| 3    | 105   |
+------+-------+