- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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 MySQL IF ELSE statement can be used in a stored procedure?
MySQL IF ELSE statement implements a basic conditional construct when the expression evaluates to false. Its syntax is as follows −
IF expression THEN statements; ELSE else-statements; END IF;
The statements must end with a semicolon.
To demonstrate the use of IF ELSE 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_IFELSE’ which have IF ELSE statements in it −
mysql> DELIMITER // ; mysql> CREATE PROCEDURE coursedetails_IFELSE(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; -> IF Sub = 'Computers' THEN -> SET S_Course = 'B.Tech(CSE)'; -> ELSE -> SET S_Course = 'Subject Not in the table '; -> END IF; -> END // Query OK, 0 rows affected (0.00 sec)
Now, we can see the result below when we invoke this procedure −
mysql> Delimiter ; //
mysql> CALL coursedetails_IFELSE('Computers', @S_Course);
Query OK, 1 row affected (0.00 sec)
mysql> Select @S_Course;
+-------------+
| @S_Course |
+-------------+
| B.Tech(CSE) |
+-------------+
1 row in set (0.00 sec)
mysql> CALL coursedetails_IFELSE ('History', @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)
Updated on: 22-Jun-2020
14K+ Views
Advertisements