The While statement allows an SQL statement or a block of statements to be repeatedly executed as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.
For more information for Transact-SQL While, check .
Note: To define a statement block, use the control-of-flow keywords BEGIN and END.
Sample Source Patterns
Basic source pattern code
Transact-SQL
The following code refers to a While Loop in Transact-SQL that iterates the @Iteration variable and controls the flow of the loop to terminate when the value of @Iteration equals 10.
Statements after the CONTINUE keyword will not be executed.
IN -> SqlServer_01.sql
CREATE OR ALTER PROCEDURE WhileDemoProcedure
AS
DECLARE @iteration INT;
SET @iteration = 1;
WHILE @iteration < 100
BEGIN
IF @iteration = 10
BREAK;
ELSE
BEGIN
SET @iteration = @iteration + 1;
CONTINUE;
SET @iteration = 2 * @iteration;
END;
END;
RETURN @iteration;
GO
DECLARE @result INT;
EXEC @result = WhileDemoProcedure;
PRINT @result;
|iteration|
|---------|
|10 |
Snowflake Scripting
As well as Transact-SQL, in Snowflake Scripting the statements after the CONTINUE keyword will not be executed.
Notice that in Snowflake Scripting it is not necessary to use the BEGIN and END keywords to define a statement block, however it can be used if required.
OUT -> SqlServer_01.sql
CREATE OR REPLACE PROCEDURE WhileDemoProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
ITERATION INT;
BEGIN
ITERATION := 1;
WHILE (:ITERATION < 100) LOOP
IF (:ITERATION = 10) THEN
BREAK;
ELSE
BEGIN
ITERATION := :ITERATION + 1;
CONTINUE;
ITERATION := 2 * :ITERATION;
END;
END IF;
END LOOP;
RETURN :ITERATION;
END;
$$;
DECLARE
RESULT INT;
BEGIN
CALL WhileDemoProcedure();
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!!
PRINT @result;
END;
Snowflake Scripting allows to use LOOP keyword instead of DO and the END LOOP expression instead of END WHILE .
WHILE (Boolean_expression) LOOP
-- statement or statement block
END LOOP;
|Iteration|
|---------|
|10 |
While with empty body Source Pattern
Transact-SQL
Please note this example was written while the IF ELSE statement was not supported, the differences in the results should disappear when support for the statement is implemented.
IN -> SqlServer_02.sql
CREATE OR ALTER PROCEDURE WhileEmptyBodyProc
AS
BEGIN
DECLARE @MyVar INT;
SET @MyVar = 1;
WHILE (@MyVar < 100)
BEGIN
IF @MyVar < 50
SET @MyVar *= 5;
ELSE
SET @MyVar *= 3;
END;
RETURN @MyVar;
END;
DECLARE @result INT;
EXEC @result = WhileEmptyBodyProc;
PRINT @result;
|result|
|------|
|125 |
Snowflake Scripting
This statement can not have an empty body in Snowflake Scripting, to solve this cases a default BREAK statement is added when an empty body is detected.
OUT -> SqlServer_02.sql
CREATE OR REPLACE PROCEDURE WhileEmptyBodyProc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
MYVAR INT;
RESULT INT;
BEGIN
BEGIN
MYVAR := 1;
WHILE (:MYVAR < 100) LOOP
IF (:MYVAR < 50) THEN
MYVAR := MYVAR * 5;
ELSE
MYVAR := MYVAR * 3;
END IF;
END LOOP;
RETURN :MYVAR;
END;
CALL WhileEmptyBodyProc();
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PRINT' NODE ***/!!!
PRINT @result;
END;
$$;
|result|
|------|
|1 |
WHILE statement outside routines (functions and procedures)
Unlike Transact-SQL, Snowflake does not support executing isolated statements like WHILE outside routines like functions or procedures. For this scenario, the statement should be encapsulated in an anonymous block, as shown in the following example.
Transact-SQL
IN -> SqlServer_03.sql
DECLARE @iteration INT;
SET @iteration = 1;
WHILE @iteration < 100
BEGIN
IF @iteration = 10
BREAK;
ELSE
BEGIN
SET @iteration = @iteration + 1;
CONTINUE;
SET @iteration = 2 * @iteration;
END;
END;
Snowflake Scripting
OUT -> SqlServer_03.sql
DECLARE
ITERATION INT;
BEGIN
ITERATION := 1;
WHILE (:ITERATION < 100) LOOP
IF (:ITERATION = 10) THEN
BREAK;
ELSE
BEGIN
ITERATION := :ITERATION + 1;
CONTINUE;
ITERATION := 2 * :ITERATION;
END;
END IF;
END LOOP;
END;
