MySQL - IF Statement

MySQL IF Statement

The IF is a compound MySQL statement which has a single or set of statements and a condition. If the value of the specified condition is TRUE then, the given statement(s) are executed. Each statement may have one of more SQL statements. An empty statement is not allowed in IF.

Syntax

Following is the syntax of the IF statement in MySQL −

IF search_condition THEN statement_list
   [ELSEIF search_condition THEN statement_list] ...
   [ELSE statement_list]
END IF

Where, statement_list is a single or set of statements that are to be executed. And search_condition is the condition of the statement.

Example 1

Following query demonstrates the usage of the IF statement with a procedure −

Delimiter //
CREATE procedure loopDemo()
   label:BEGIN
      DECLARE val INT ;
      DECLARE result VARCHAR(255);
      SET val =1;
      SET result = '';
         loop_label: LOOP
         IF val > 10 THEN
            LEAVE loop_label;
         END IF;
         SET result = CONCAT(result,val,',');
         SET val = val + 1;
         ITERATE loop_label;
      END LOOP;
      SELECT result;
   END//

You can call the above procedure as follows −

call loopDemo;//

Output

Following is the output of the above query −

Example 2

Following query demonstrates how to use the IF statement with in a function. −

DELIMITER //
CREATE FUNCTION Sample (bonus INT)
   RETURNS INT
   BEGIN
      DECLARE income INT;
      SET income = 0;
      myLabel: LOOP
         SET income = income + bonus;
         IF income < 10000 THEN
            ITERATE myLabel;
         END IF;
         LEAVE myLabel;
      END LOOP myLabel;
   RETURN income;
END; //
DELIMITER ;

You can call the above created function as shown below −

SELECT Sample(1000);

Output

The above query produces the following output −