Skip to main content
Skip table of contents

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 that is 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.

Example

SQL
BEGIN 
	LOOP ON (SELECT id FROM "SYSADMIN.ScheduleJobs") AS JOB 
	BEGIN 
		CALL SYSADMIN.createSchedule(jobId => job.id, type => 'once', intervl => 0, startDelay => 0, enabled => true); 
	END 
END;;

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

SQL
[label :] WHILE <criteria>
    block

Syntax

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

Example

SQL
BEGIN 
	DECLARE integer i = 0; 
	WHILE (i < 5) BEGIN 
		SELECT i; i = i + 1; 
	END 
END ;;

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.

Example

SQL
BEGIN 
	DECLARE integer i = 0; 
	DECLARE string str = 0; 
	WHILE (i < 5) 
		BEGIN 
			i = i + 1; 
			IF (i in (2, 3)) 
				CONTINUE; 
			str = str || i; 
		END 
	SELECT str; 
END ;;

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.

Example

SQL
BEGIN 
	DECLARE integer i = 0; 
	WHILE (i < 5) BEGIN 
		IF(i = 3) 
			BREAK; 
		SELECT i; 
		i = i + 1; 
	END
END ;;

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.

Example

SQL
BEGIN 
	DECLARE string str = 0; 
	LABEL_A: BEGIN 
			BEGIN 
				str = 'a'; 
				LEAVE LABEL_A; 
			END 
			str = 'b'; 
		END 
	SELECT str; 
END ;;
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.