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

A LOOP loop does not specify a number of iterations or a terminating condition. The user must explicitly exit the loop by using BREAK or RETURN inside the loop.

For more information on loops, see Working with loops.

See also:

BREAK, CONTINUE, RETURN

Syntax¶

LOOP ; [ ; ... ] END LOOP [ ] ;

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.

_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¶

• A LOOP repeats until a BREAK or RETURN is executed. The BREAK or RETURN command is almost always inside a conditional expression (e.g. IF or CASE).
• 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 loop inserts predictable test data into a table:

CREATE TABLE dummy_data (ID INTEGER);

CREATE PROCEDURE break_out_of_loop() RETURNS INTEGER LANGUAGE SQL AS DECLAREcounterINTEGER;BEGINcounter:=0;LOOPcounter:=counter+1;IF(counter>5)THENBREAK;ENDIF;INSERTINTOdummydata(ID)VALUES(:counter);ENDLOOP;RETURNcounter;END;DECLARE counter INTEGER; BEGIN counter := 0; LOOP counter := counter + 1; IF (counter > 5) THEN BREAK; END IF; INSERT INTO dummy_data (ID) VALUES (:counter); END LOOP; RETURN counter; END;DECLAREcounterINTEGER;BEGINcounter:=0;LOOPcounter:=counter+1;IF(counter>5)THENBREAK;ENDIF;INSERTINTOdummyd​ata(ID)VALUES(:counter);ENDLOOP;RETURNcounter;END; ;

Here is the output of executing the stored procedure:

CALL break_out_of_loop(); +-------------------+

Here is the content of the table after calling the stored procedure:

SELECT * FROM dummy_data ORDER BY ID; +----+