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)