13.6.5.2. IF Syntax

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

The IF statement for stored programs implements a basic conditional construct.

If the search_condition evaluates to true, the corresponding THEN or ELSEIF clause statement_list executes. If no search_condition matches, the ELSE clause statement_list executes.

Each statement_list consists of one or more SQL statements; an empty statement_list is not permitted.

An IF ... END IF block, like all other flow-control blocks used within stored programs, must be terminated with a semicolon, as shown in this example:

DELIMITER //CREATE FUNCTION SimpleCompare(n INT, m INT)  RETURNS VARCHAR(20)  BEGIN    DECLARE s VARCHAR(20);    IF n > m THEN SET s = '>';    ELSEIF n = m THEN SET s = '=';    ELSE SET s = '<';    END IF;    SET s = CONCAT(n, ' ', s, ' ', m);    RETURN s;  END //DELIMITER ;

As with other flow-control constructs, IF ... END IF blocks may be nested within other flow-control constructs, including other IF statements. Each IF must be terminated by its own END IF followed by a semicolon. You can use indentation to make nested flow-control blocks more easily readable by humans (although this is not required by MySQL), as shown here:

DELIMITER //CREATE FUNCTION VerboseCompare (n INT, m INT)  RETURNS VARCHAR(50)  BEGIN    DECLARE s VARCHAR(50);    IF n = m THEN SET s = 'equals';    ELSE      IF n > m THEN SET s = 'greater';      ELSE SET s = 'less';      END IF;      SET s = CONCAT('is ', s, ' than');    END IF;    SET s = CONCAT(n, ' ', s, ' ', m, '.');    RETURN s;  END //DELIMITER ;

In this example, the inner IF is evaluated only if n is not equal to m.