How Can MySQL CASE statement be used in stored procedure?

Actually, the CASE statement has the functionality of an IF-THEN-ELSE statement. It has the following syntax −

CASE
WHEN condition_1 THEN
   {...statements to execute when condition_1 is TRUE...}
[ WHEN condition_2 THEN
   {...statements to execute when condition_2 is TRUE...} ]
[ WHEN condition_n THEN
   {...statements to execute when condition_n is TRUE...} ]
[ ELSE
   {...statements to execute when all conditions were FALSE...} ]
END CASE;

The CASE statement will execute the ELSE clause if none of the WHEN clauses were executed.

To demonstrate the use of CASE statement within MySQL stored procedure, we are creating the following stored procedure which is based on the values, as shown below, of the table named ‘student_info’ −

mysql> Select * from student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Jaipur     | Literature |
| 125  | Raman   | Shimla     | Computers  |
+------+---------+------------+------------+
3 rows in set (0.00 sec)

The following query will create a procedure named ‘coursedetails_CASE’ which have a CASE statement in it −

mysql> Delimiter //
mysql> CREATE PROCEDURE coursedetails_CASE(IN S_subject Varchar(20), OUT S_Course Varchar(50))
   -> BEGIN
   -> DECLARE SUB VArchar(20);
   -> SELECT SUBJECT INTO Sub
   -> FROM Student_Info WHERE S_Subject = Subject;
   -> CASE S_Subject WHEN 'Computers' THEN
   -> SET S_Course = 'B.Tech(CSE)’;
   -> WHEN 'History' THEN
   -> SET S_Course = 'Masters in History';
   -> WHEN 'Literature' THEN
   -> SET S_Course = 'Masters in English';
   -> ELSE
   -> SET S_Course = 'Subject not in the table';
   -> END CASE ;
   -> END //
Query OK, 0 rows affected (0.11 sec)

Now, we can see the result below when we invoke this procedure −

mysql> DELIMITER ;
mysql> CALL coursedetails_CASE ('Computers', @S_course);
Query OK, 1 row affected (0.08 sec)

mysql> Select @S_Course;
+-------------+
| @S_Course   |
+-------------+
| B.Tech(CSE) |
+-------------+
1 row in set (0.00 sec)

mysql> CALL coursedetails_CASE ('literature', @S_course);
Query OK, 1 row affected (0.00 sec)

mysql> Select @S_Course;
+--------------------+
| @S_Course          |
+--------------------+
| Masters in English |
+--------------------+
1 row in set (0.00 sec)

mysql> CALL coursedetails_CASE ('Math', @S_course);
Query OK, 0 rows affected (0.00 sec)

mysql> Select @S_Course;
+--------------------------------+
| @S_Course                      |
+--------------------------------+
| Subject Not in the table       |
+--------------------------------+
1 row in set (0.00 sec)

mysql> CALL coursedetails_CASE ('History', @S_course);
Query OK, 1 row affected (0.01 sec)

mysql> Select @S_Course;
+--------------------+
| @S_Course          |
+--------------------+
| Masters in History |
+--------------------+
1 row in set (0.00 sec)

Sreemaha

Updated on 22-Jun-2020 06:10:19

• Related Questions & Answers
• How MySQL IF statement can be used in a stored procedure?
• How MySQL WHILE loop statement can be used in stored procedure?
• How MySQL REPEAT loop statement can be used in stored procedure?
• How Can MySQL LOOP statement be used in a stored procedure?
• How MySQL IF ELSE statement can be used in a stored procedure?
• How can MySQL IF ELSEIF ELSE statement be used in a stored procedure?
• How can user variables be used in MySQL stored procedure?
• How can local variables be used in MySQL stored procedure?
• How can column data be used within MySQL CASE statement?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• How can we invoke MySQL stored procedure?
• How can we alter a MySQL stored procedure?
• How can we drop a MySQL stored procedure?
• How to call a stored procedure using select statement in MySQL?
• How to correctly implement END IF statement in a MySQL Stored Procedure?