WHILE
Translation reference to convert SQL Server While Statement to Snowflake Scripting
Description
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 SQL Server While, check here.
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }
Note: To define a statement block, use the control-of-flow keywords BEGIN
and END
.
Sample Source Patterns
Basic source pattern code
SQL Server
The following code refers to a While Loop in SQL Server that iterates the @Iteration variable and controls the flow of the loop to terminate when the value of @Iteration equals 10.
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;
Snowflake Scripting
CREATE OR REPLACE PROCEDURE PUBLIC.WhileDemoProcedure()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
LET iteration INTEGER;
iteration := 1;
WHILE (iteration < 100) DO
IF (iteration = 10) THEN
BREAK;
ELSE
iteration := iteration + 1;
CONTINUE;
iteration := 2 * iteration;
END IF;
END WHILE;
RETURN iteration;
END;
$$;
CALL WhileDemoProcedure();
While with empty body Source Pattern
SQL Server
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;
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.
CREATE OR REPLACE PROCEDURE WhileEmptyBodyProc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
MYVAR INT;
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();
WHILE statement outside routines (functions and procedures)
Unlike SQL Server, 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.
SQL Server
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
DECLARE
ITERATION INT;
BEGIN
ITERATION := 1;
WHILE (:ITERATION < 100) LOOP
IF (:ITERATION = 10) THEN
BREAK;
ELSE
BEGIN
ITERATION := :ITERATION + 1;
-- --** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FLOW INTERRUPTION STATEMENT' NODE **
-- CONTINUE;
ITERATION := 2 * :ITERATION;
END;
END IF;
END LOOP;
END;
Known Issues
No issues were found.
Related EWIs
No related EWIs.
Last updated
Was this helpful?