WHILE STATEMENT

Executes a list of SQL statements in a loop until the WHILE condition is met

Grammar Syntax

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

Click here to go to the BigQuery specification for this syntax.

Sample Source Patterns

Without block

BigQuery

DECLARE x INT64 DEFAULT 0;
WHILE x < 5 DO
  SET x = x + 1;
END WHILE;
INSERT INTO SNOWPUBLIC.table_while_1 VALUES (x);

Result

Row	id
1	5

Snowflake

SET x = 0;
BEGIN
  WHILE ((
    SELECT
      $x < 5
  )) DO
    SET x = $x + 1;
  END WHILE;
END;
INSERT INTO SNOWPUBLIC.table_while_1
VALUES ($x);

Result

Row	id
1	5

Inside procedure

BigQuery

CREATE OR REPLACE PROCEDURE SNOWPUBLIC.proc_while()
BEGIN 
DECLARE x INT64 DEFAULT 0;
WHILE x < 3 DO
  SET x = x + 1;
END WHILE;
    INSERT INTO SNOWPUBLIC.table_while_1 VALUES (x);
RETURN;
END;

Result

Row	id
1	3

Snowflake

CREATE OR REPLACE PROCEDURE SNOWPUBLIC.proc_while ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    LET x INT DEFAULT 0;
    WHILE (x < 3) DO
      x := x + 1;
    END WHILE;
    INSERT INTO SNOWPUBLIC.table_while_1
    VALUES (:x);
    RETURN 'SUCCESS';
  END;
$$;

Result

Row	id
1	3