IF Statement

The IF statement is used in stored programs that implement the basic conditional construct in MySQL. Based on a certain condition, it allows us to execute a set of SQL statements. It returns one of the three values True, False, or NULL.

We can use this statement in three ways IF-THEN, IF-THEN-ELSE, IF-THEN-ELSEIF-ELSE clauses, and can terminate with END-IF. Let us see each of these statements in detail.

IF-THEN Statement

This statement executes a set of SQL queries based on certain conditions or expressions. The syntax of the IF-THEN statement is as follows:

 IF condition THEN   

   statements;  

END IF;

In the above syntax, we have to specify a condition for executing the code. If the statement evaluates to true, it will execute the statement between IF-THEN and END-IF. Otherwise, it will execute the statement following the END-IF.

Example

The IF…ENDIF block executes with stored programs and terminates with a semicolon, as shown in the below example.

DELIMITER $$  

CREATE PROCEDURE myResult(original_rate NUMERIC(6,2),OUT discount_rate NUMERIC(6,2))  

     NO SQL  

      BEGIN  

         IF (original_rate>200) THEN  

            SET discount_rate=original_rate*.5;  

         END IF;  

         select discount_rate;  

     END$$  

DELIMITER $$;

Next, take two variables and set the value for both as below:

mysql> set @p = 600;  

mysql> set @dp = 500;

Now, call the stored procedure function to check the output.

mysql> call myResult(@p, @dp)

We will get the following output:

IF-THEN-ELSE Statement

If we want to execute other statements when the condition specifies in the IF block does not evaluate to true, this statement can be used. The syntax of an IF-THEN-ELSE statement is given below:

IF condition THEN  

   statements;  

ELSE  

   else-statements;  

END IF;

In the above syntax, we have to specify a condition for executing the code. If the statement evaluates to true, it will execute the statement between IF-THEN and ELSE. Otherwise, it will execute the statement following the ELSE and END-IF.

Let us modify the above myResult() stored procedure. So, first, remove myResult() stored procedure by using the command below:

Mysql> DROP procedure myResult;

Next, write the new code for this, as shown below:

 DELIMITER $$  

CREATE PROCEDURE myResult(original_rate NUMERIC(6,2),OUT discount_rate NUMERIC(6,2))  

     NO SQL  

      BEGIN  

         IF (original_rate>200) THEN  

            SET discount_rate=original_rate*.5;  

        ELSE  

            SET discount_rate=original_rate;  

         END IF;  

         select discount_rate;  

     END$$  

DELIMITER ;

Next, create two variables and set the value for both as below:

  mysql> set @p = 150;  

mysql> set @dp = 180;

Now, call the stored procedure function to get the output.

mysql> call myResult(@p, @dp)

It will give the following output:

IF-THEN-ELSEIF-ELSE Statement

If we want to execute a statement based on multiple conditions, this statement can be used. The syntax of the IF-THEN-ELSE statement is given below:

IF condition THEN  

   statements;  

ELSEIF elseif-condition THEN  

   elseif-statements;  

...  

ELSE  

   else-statements;  

END IF;

In the above syntax, if the condition becomes true, it will execute the IF-THEN branch. Otherwise, it will evaluate elseif-condition. When the elseif-condition becomes true, it will execute the elseif-statement. If this condition is also false, it will evaluate the next elseif-condition. Thus, here we will evaluate multiple elseif-condition, and if any condition in the IF and ELSE-IF does not becomes true, it will execute the statement of the ELSE branch.

Let us modify the above myResult() stored procedure. So, first, remove myResult() stored procedure by using the command below:

Mysql> DROP procedure myResult;