IF

You can use the IF statement to build a basic conditional statement.

Syntax:

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

The IF statement can contain the THEN, ELSE, and ELSEIF clauses and ends with END IF.

If the evaluation result of search_condition is True, the statements in the THEN or ELSEIF clause are executed. If no search_condition expression has the value True, the statement_list in the ELSE clause is executed.

Each statement_list consists of one or more SQL statements and must not be empty.

The IF ... END IF block in a stored program must end with a semicolon (;). Like other throttling constructs, the IF ... END IF block can be nested in other control statements, including the IF statement. Each IF statement must end with its own END IF;. You can indent the code to make the nested throttling block easier to read, as shown in the following example:

obclient> DELIMITER //

obclient> CREATE FUNCTION comp_fuc (n INT, m INT)
   RETURNS VARCHAR(50)

   BEGIN
     DECLARE x VARCHAR(50);

     IF n = m THEN SET x = 'Equal to';
     ELSE
       IF n > m THEN SET x = 'Greater than';
       ELSE SET x = 'Smaller than';
       END IF;

     END IF;

     SET x = CONCAT(n, ' ', x, ' ', m, '.');
     RETURN x;
   END //
Query OK, 0 rows affected

In the preceding example, the internal IF statement is executed only when n is not equal to m.