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.

Statements after the CONTINUE keyword will not be executed.

While

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;

Result

Snowflake Scripting

As well as SQL Server, 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.

While

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();

Loop keyword

Result

While with empty body Source Pattern

SQL Server

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.

Query

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

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.

Query

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();

Result

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.