- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- 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 local variables be used in MySQL stored procedure?
- How can user 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 to call a stored procedure using select statement in MySQL?
- How can we alter a MySQL stored procedure?
- How can we drop a MySQL stored procedure?
- How to correctly implement END IF statement in a MySQL Stored Procedure?

Advertisements