How can MySQL IF ELSEIF ELSE statement be used in a stored procedure?

MySQL IF ELSEIF ELSE execute the statements based on multiple expressions Its syntax is as follows −

IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
… … … …
ELSE
  else-statements;
END IF;

The statements must end with a semicolon.

To demonstrate the use of IF ELSEIF 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_IF_ELSEIF’ which have IF ELSEIF ELSE statements in it −

mysql> DELIMITER // ;
mysql> CREATE PROCEDURE coursedetails_IF_ELSEIF(IN S_Subject Varchar(20), OUT S_Course varchar(20))
    -> 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)';
    -> ELSEIF Sub = 'History' THEN
    -> SET S_Course = 'Masters in History';
    -> ELSEIF Sub = 'Literature' THEN
    -> SET S_Course = 'Masters in English';
    -> 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_IF_ELSEIF('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_IF_ELSEIF ('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)

Abhinaya

e

Updated on: 22-Jun-2020

9K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started