Control Structures

If Statement

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

Usage
IF (criteria)
block
[ELSE
block]
Example If Statement
BEGIN
IF (var1 = 'North America')
BEGIN
...
END
ELSE
BEGIN
...
END
END

Tip

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

Loop Statement

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

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

Syntax Rules

  • 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 that is used to execute a block repeatedly whenever a specified condition is met.

Usage
BEGIN
[label :] WHILE <criteria>
block
END

Syntax Rules

  • 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
CONTINUE [label];

Syntax Rules:

  • 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
BREAK [label];

Syntax Rules:

  • 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 construct to leave to the specified level.

Usage
LEAVE label;

Syntax Rules:

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