Flow Control Statements

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

IF implements a basic conditional construct. If the search_condition evaluates to true, the corresponding SQL statement list is executed. If no search_condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.

Note: There is also an IF() function, which differs from the IF statement.

There are two forms of 'CASE' statement.

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

or

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

The CASE statement for stored routines implements a complex conditional construct. If a search_condition evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.

Note: The syntax of the CASE statement shown here for use inside stored routines differs slightly from that of the SQL CASE expression. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END

Examples:

...
CASE inValue
    WHEN 1 THEN INSERT INTO t1 VALUES( 1 );
    WHEN 2 THEN INSERT INTO t1 VALUES( 2 );
ELSE
    INSERT INTO t1 VALUES( 3 );
END CASE
...

...
CASE
    WHEN inValue = 1 THEN   INSERT INTO t1 VALUES( 1 );
    WHEN (inValue = 2) THEN INSERT INTO t1 VALUES( 2 );
ELSE
    INSERT INTO t1 VALUES( 3 );
END CASE
...