A FOR loop repeats a sequence of steps for a specified number of times or for each
row in a result set. Snowflake Scripting supports the following types of FOR loops:
EXECUTEIMMEDIATE$$
DECLARE
counter INTEGER DEFAULT 0;
maximum_count INTEGER default 5;
BEGIN
FOR i IN 1 TO maximum_count DO
counter := counter + 1;
END FOR;
RETURN counter;
END;
$$;
You can include SQL statements inside Snowflake Scripting loops. For example, the following FOR loop executes an INSERT
statement five times to insert the value of the counter into a table:
EXECUTEIMMEDIATE$$
DECLARE
counter INTEGER DEFAULT 0;
maximum_count INTEGER default 5;
BEGIN
CREATE OR REPLACE TABLE test_for_loop_insert(i INTEGER);
FOR i IN 1 TO maximum_count DO
INSERT INTO test_for_loop_insert VALUES (:i);
counter := counter + 1;
END FOR;
RETURN counter || ' rows inserted';
END;
$$;
The following example uses a counter-based FOR loop to populate a date dimension table, which is a common task when setting up
a data warehouse. The loop iterates over a range of days and inserts a row for each date with computed attributes:
DECLARE
start_date DATEDEFAULT'2025-01-01';
current_date_val DATE;BEGINCREATEORREPLACETABLE date_dimension (
date_key INTEGER,
full_date DATE,
day_of_week VARCHAR,
month_name VARCHAR,quarterINTEGER,yearINTEGER);FOR i IN1TO7DO
current_date_val :=DATEADD('day',:i-1,:start_date);INSERTINTO date_dimension
SELECT:i,:current_date_val,DAYNAME(:current_date_val),MONTHNAME(:current_date_val),QUARTER(:current_date_val),YEAR(:current_date_val);ENDFOR;RETURN'Populated date dimension with 7 rows';END;
EXECUTEIMMEDIATE$$
DECLARE
start_date DATE DEFAULT '2025-01-01';
current_date_val DATE;
BEGIN
CREATE OR REPLACE TABLE date_dimension (
date_key INTEGER,
full_date DATE,
day_of_week VARCHAR,
month_name VARCHAR,
quarter INTEGER,
year INTEGER
);
FOR i IN 1 TO 7 DO
current_date_val := DATEADD('day', :i - 1, :start_date);
INSERT INTO date_dimension
SELECT :i, :current_date_val, DAYNAME(:current_date_val),
MONTHNAME(:current_date_val), QUARTER(:current_date_val),
YEAR(:current_date_val);
END FOR;
RETURN 'Populated date dimension with 7 rows';
END;
$$;
+----------------------------------------+| anonymous block||----------------------------------------|| Populated date dimension with7rows|+----------------------------------------+
To verify the results, query the table:
SELECT*FROM date_dimension ORDERBY date_key;
+----------+------------+-------------+------------+---------+------+| DATE_KEY | FULL_DATE | DAY_OF_WEEK | MONTH_NAME |QUARTER|YEAR||----------+------------+-------------+------------+---------+------||1|2025-01-01| Wed | Jan |1|2025||2|2025-01-02| Thu | Jan |1|2025||3|2025-01-03| Fri | Jan |1|2025||4|2025-01-04| Sat | Jan |1|2025||5|2025-01-05| Sun | Jan |1|2025||6|2025-01-06| Mon | Jan |1|2025||7|2025-01-07| Tue | Jan |1|2025|+----------+------------+-------------+------------+---------+------+
The following example uses a FOR loop to iterate over the rows in a cursor for the invoices table:
DECLARE
total_price FLOAT;
c1 CURSORFORSELECT price FROM invoices;BEGIN
total_price :=0.0;FOR record IN c1 DO
total_price := total_price + record.price;ENDFOR;RETURN total_price;END;
EXECUTEIMMEDIATE$$
DECLARE
total_price FLOAT;
c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
total_price := 0.0;
FOR record IN c1 DO
total_price := total_price + record.price;
END FOR;
RETURN total_price;
END;
$$;
The following example uses a cursor-based FOR loop to iterate over a table of employees, give each employee a raise based on
their department, and insert an audit record for each update:
SELECT emp_id,name, department, salary FROM loop_test_employees ORDERBY emp_id;
+--------+-------+-------------+-----------+| EMP_ID |NAME| DEPARTMENT | SALARY ||--------+-------+-------------+-----------||1| Alice | Engineering |99000.00||2| Bob | Sales |73500.00||3| Carol | Engineering |104500.00||4| Dave | Sales |75600.00|+--------+-------+-------------+-----------+
SELECT emp_id, old_salary, new_salary FROM salary_audit ORDERBY emp_id;
EXECUTEIMMEDIATE$$
DECLARE
total_price FLOAT;
rs RESULTSET;
BEGIN
total_price := 0.0;
rs := (SELECT price FROM invoices);
FOR record IN rs DO
total_price := total_price + record.price;
END FOR;
RETURN total_price;
END;
$$;
The following example uses a RESULTSET-based FOR loop to validate customer records. It checks each record for required contact
information and updates the status column to mark each record as verified or incomplete. This type of data-quality check
is a common step in extract, transform, and load (ETL) pipelines:
EXECUTEIMMEDIATE$$
DECLARE
rs RESULTSET;
valid_count INTEGER DEFAULT 0;
invalid_count INTEGER DEFAULT 0;
cur_customer_id INTEGER;
BEGIN
rs := (SELECT customer_id, customer_email, customer_phone FROM loop_test_customers WHERE status = 'pending_review');
FOR record IN rs DO
cur_customer_id := record.customer_id;
IF (record.customer_email IS NOT NULL AND record.customer_phone IS NOT NULL) THEN
UPDATE loop_test_customers SET status = 'verified' WHERE customer_id = :cur_customer_id;
valid_count := valid_count + 1;
ELSE
UPDATE loop_test_customers SET status = 'incomplete' WHERE customer_id = :cur_customer_id;
invalid_count := invalid_count + 1;
END IF;
END FOR;
RETURN 'Verified: ' || valid_count || ', Incomplete: ' || invalid_count;
END;
$$;
+-------------+-----------------+-------------------+----------------+------------+| CUSTOMER_ID | CUSTOMER_NAME | CUSTOMER_EMAIL | CUSTOMER_PHONE | STATUS ||-------------+-----------------+-------------------+----------------+------------||1| Alice Smith | alice@example.com |800-555-0101| verified ||2| Bob Jones |NULL|800-555-0102| incomplete ||3| Carol White | carol@example.com |NULL| incomplete ||4| Dave Brown |NULL|NULL| incomplete ||5| Eve Davis | eve@example.com |800-555-0105| verified |+-------------+-----------------+-------------------+----------------+------------+
A WHILE loop iterates while a condition is true. In a WHILE
loop, the condition is tested immediately before executing the body of the loop. If the condition is false before the first
iteration, then the body of the loop does not execute even once.
The following example uses a WHILE loop to build a daily sales summary from raw transaction data. It processes one date at
a time, aggregating the transactions for that date into a summary row and marking them as loaded. This date-by-date
summarization pattern is common in extract, transform, and load (ETL) pipelines:
EXECUTEIMMEDIATE$$
DECLARE
next_date DATE;
BEGIN
next_date := (SELECT MIN(txn_date) FROM loop_test_raw_transactions WHERE NOT loaded);
WHILE (next_date IS NOT NULL) DO
INSERT INTO loop_test_daily_sales_summary
SELECT txn_date, SUM(amount), COUNT(*)
FROM loop_test_raw_transactions
WHERE txn_date = :next_date AND NOT loaded
GROUP BY txn_date;
UPDATE loop_test_raw_transactions SET loaded = TRUE WHERE txn_date = :next_date;
next_date := (SELECT MIN(txn_date) FROM loop_test_raw_transactions WHERE NOT loaded);
END WHILE;
RETURN 'Daily summaries created for all transaction dates';
END;
$$;
+----------------------------------------------------+| anonymous block||----------------------------------------------------||Daily summaries created foralltransaction dates |+----------------------------------------------------+
A REPEAT loop iterates until a condition is true. In a REPEAT
loop, the condition is tested immediately after executing the body of the loop. As a result, the body of the loop always executes
at least once.
The following example creates a staging table and a target table with an additional batch_id column. It then uses a
REPEAT loop to move rows in batches from the staging table to the target table, incrementing the batch ID after each
iteration, until no rows remain in the staging table:
A LOOP loop executes until a BREAK
command is executed. A BREAK command is normally embedded inside branching logic
(e.g. IF statements or CASE statements).
The following example creates a log table with entries spanning multiple dates. It uses a LOOP to archive rows older
than a cutoff date into an archive table and delete them from the source, processing one day at a time until no
qualifying rows remain:
You can explicitly terminate a loop early by executing the BREAK command.
BREAK (and its synonym EXIT) immediately stops the current iteration, and skips any remaining iterations.
You can think of BREAK as jumping to the first executable statement after the end of the loop.
BREAK is required in a LOOP loop but is not necessary in WHILE, FOR, and REPEAT loops. In most cases,
if you have statements that you want to skip, you can use the standard branching constructs (IF statements and
CASE statements) to control which statements inside a loop are executed.
A BREAK command itself is usually inside an IF or CASE statement.
Terminating an iteration without terminating the loop¶
You can use the CONTINUE (or ITERATE) command to jump to the end of an iteration of a loop, skipping the
remaining statements in the loop. The loop continues at the start of the next iteration.
Such jumps are rarely necessary. In most cases, if you have statements that you want to skip, you can use the
standard branching constructs (IF statements and CASE statements) to control which
statements inside a loop are executed.
A CONTINUE or ITERATE command itself is usually inside an IF or CASE statement.
Specifying where execution should continue after termination¶
In a BREAK or CONTINUE command, if you need to continue execution at a specific point in the code (e.g. the outer
loop in a nested loop), specify a label that identifies the point at which execution should continue.
The following example demonstrates this in a nested loop:
inner_counter is incremented up to 5. CONTINUE OUTER starts a new iteration of the outer loop, which starts a new
iteration of the inner loop, which increments this counter up to 5. These iterations continue until the value of
inner_counter equals 5 and BREAK OUTER terminates the inner loop.
outer_counter is never incremented. The statement that increments this counter is never reached because BREAK OUTER
transfers control to the end of the outer loop.