LOOP (Snowflake Scripting)¶

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.

Note

This Snowflake Scripting construct is valid only within a Snowflake Scripting block.

See also:: BREAK, CONTINUE, RETURN

Syntax¶

LOOP
    <statement>;
    [ <statement>; ... ]
END LOOP [ <label> ] ;

Copy

Where:

statement
A statement can be any of the following:

A single SQL statement (including CALL).

A control-flow statement (for example, a looping or branching statement).

A nested block.

label
An optional label. Such a label can be a jump target for a BREAK or CONTINUE statement. A label must follow the naming rules for Object 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
$$
    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;
$$
;

Copy

Here is the output of executing the stored procedure:

CALL break_out_of_loop();
+-------------------+
| BREAK_OUT_OF_LOOP |
|-------------------|
|                 6 |
+-------------------+

Copy

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

SELECT *
    FROM dummy_data
    ORDER BY ID;
+----+
| ID |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

Copy