13.6.5.2 IF Statement
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.
There is also an IF()
function, which differs from the IF
statement described here. See Section 12.4, “Control Flow Functions”. The IF
statement can have THEN
, ELSE
, and ELSEIF
clauses, and it is terminated with END IF
.
If a given 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
.