Control Structures

IF Statement

An IF statement evaluates a condition and executes one of two statements depending on the result of the evaluation. You can nest IF statements to create complex branching logic. A dependent ELSE statement will be executed only if the IF statement evaluates to false.

Usage

SQL

IF (criteria)
    block
[ELSE
    block]

Example

SQL

BEGIN
DECLARE STRING var1 = 'South America';
IF (var1 = 'North America')
    BEGIN
        ...
    END
ELSE
    BEGIN
        ...
    END
END

NULL values should be considered in the criteria of an IF statement. IS NULL can be used to detect the presence of a NULL value.

Loop Statement

A LOOP statement is an iterative control construct used to cursor through a result set.

Usage

SQL

[label :] LOOP ON (<select statement>) AS <cursorname>
    block

Syntax

The label must not be the same as any other label used in statements containing this one.

While Statement

A WHILE statement is an iterative control construct used to execute a block repeatedly whenever a specified condition is met.

Usage

SQL

[label :] WHILE <criteria>
    block

Syntax

The label must not be the same as any other label used in statements containing this one.

Continue Statement

A CONTINUE statement is used inside a LOOP or WHILE construct to continue with the next loop by skipping over the rest of the statements in the loop. It must be used inside a LOOP or WHILE statement.

Usage

SQL

CONTINUE [label];

Syntax

If the label is specified, it must exist on a containing LOOP or WHILE statement;
If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.

Break Statement

A BREAK statement is used inside a LOOP or WHILE construct to break from the loop. It must be used inside a LOOP or WHILE statement.

Usage

SQL

BREAK [label];

Syntax

If the label is specified, it must exist on a containing LOOP or WHILE statement;
If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.

Leave Statement

A LEAVE statement is used inside a compound, LOOP, or WHILE constructs to leave to the specified level.

Usage

SQL

LEAVE label;

Syntax

The label must exist on a containing compound statement, LOOP, or WHILE statement.