REPEAT (Snowflake Scripting) | Snowflake Documentation (original) (raw)

A REPEAT loop iterates until a specified condition is true. A REPEAT loop tests the condition at the end of the loop. This means that the body of a REPEAT loop always executes at least once.

For more information on loops, see Working with loops.

See also:

BREAK, CONTINUE

Syntax¶

REPEAT ; [ ; ... ] UNTIL ( ) END REPEAT [ ] ;

Where:

_statement_

A statement can be any of the following:

• A single SQL statement (including CALL).
• A control-flow statement (for example, a looping orbranching statement).
• A nested block.

_condition_

An expression that evaluates to a BOOLEAN.

_label_

An optional label. Such a label can be a jump target for a BREAK orCONTINUE statement. A label must follow the naming rules forObject identifiers.

Usage notes¶

• Put parentheses around the condition in the REPEAT. For example: REPEAT ( <condition> ).
• If the _condition_ never evaluates to TRUE, and the loop does not contain aBREAK command (or equivalent), then the loop will run and consume credits indefinitely.
• If the _condition_ is NULL, then it is treated as FALSE.
• A loop can contain multiple statements. You can use, but are not required to use, a BEGIN … END block to contain those statements.

Examples¶

This example uses a loop to calculate a power of 2. (This is an inefficient solution, but it does demonstrate looping.)

CREATE PROCEDURE power_of_2() RETURNS NUMBER(8, 0) LANGUAGE SQL AS DECLAREcounterNUMBER(8,0);−−Loopcounter.powerof2NUMBER(8,0);−−Storesthemostrecentpowerof2thatwecalculated.BEGINcounter:=1;powerof2:=1;REPEATpowerof2:=powerof2∗2;counter:=counter+1;UNTIL(counter>8)ENDREPEAT;RETURNpowerof2;END;DECLARE counter NUMBER(8, 0); -- Loop counter. power_of_2 NUMBER(8, 0); -- Stores the most recent power of 2 that we calculated. BEGIN counter := 1; power_of_2 := 1; REPEAT power_of_2 := power_of_2 * 2; counter := counter + 1; UNTIL (counter > 8) END REPEAT; RETURN power_of_2; END;DECLAREcounterNUMBER(8,0);−−Loopcounter.powero​f2​NUMBER(8,0);−−Storesthemostrecentpowerof2thatwecalculated.BEGINcounter:=1;powero​f2​:=1;REPEATpowero​f2​:=powero​f2​∗2;counter:=counter+1;UNTIL(counter>8)ENDREPEAT;RETURNpowero​f2​;END;;

Here is the output of executing the stored procedure:

CALL power_of_2(); +------------+