Snowflake Scripting Control Structures

In my other Snowflake article, we have checked how JavaScript control structure works in Snowflake JavaScript stored procedures. In this article, we will check Snowflake scripting control structures such as IF, WHILE, FOR, REPEAT and LOOP.

The best part about Snowflake is it supports both SQL and JavaScript as a programming language to write stored procedures and user defined functions. Till now, it was supported only JavaScript to write stored procedures, but now they started supporting Snowflake script much similar to PL/SQL programming language in other databases such as Oracle, Teradata, etc.

The Snowflake scripting provides a powerful control structure such as branching and looping. You can use these structures to manipulate data.

Following are the Snowflake scripting control structures

Branching Structures – Conditional control structures
Looping Structures – Iterative control structures.

Snowflake Branching Structures – Conditional control structures

Snowflake script supports the IF statement as a conditional control structure statement. An IF statement provides a way to execute a set of statements if a condition is met.

IF Statement

IF Statement in Snowflake Script Stored Procedures and UDFs

The if statements can perform a particular task based on the certain conditions. There are three forms of IF statement that you can use. For example,

if
if-else
if-else-if

Simple if Statement in Snowflake Scripting

Use the simple if statement to specify a block of code to be executed if a condition is true.

For example,

IF ( <condition> ) THEN
    <statement>;
END IF;

if-else Statement in Snowflake

You can specify else block along with if to specify a block of code to be executed if the condition is false.

For example,

IF ( <condition> ) THEN
    <statement>;
ELSE
    <statement>;
END IF;

if-else-if Statement in Snowflake

You can also specify the elseif statement to specify a new condition in else block.

For example,

IF ( <condition> ) THEN
    <statement>;
ELSEIF ( <condition> ) THEN
    <statement>;
ELSE
    <statement>;
END IF;

Snowflake Script Stored Procedure with IF ELSE Block

Following Snowflake script stored procedure demonstrates the use of if else block statement.

create or replace procedure example_ifelse(input integer)
returns varchar
language sql
as
$$
begin
    if (input = 1) then
        return 'one';
    elseif (input = 2) then
        return 'two';
    else
        return 'Unexpected input.';
    end if;
end;
$$ ;

Snowflake Looping Structures – Iterative control structures

Iterative control structures are commonly used in stored procedures and UDF statements. With the help of iterative control statements, you can control the flow of execution in your stored procedures and user defined functions.

Snowflake scripting supports following iterative control structures:

WHILE
FOR
REPEAT
LOOP

Now, let us check these looping structures in detail.

While loop Inside Snowflake Script Stored Procedures and User Defined Functions

The while loop loops through a block of code inside a stored procedure or user defined function as long as a specified condition is true. You can also use while loop in the Anonymous Snowflake scripting block. The WHILE loop syntax is similar to JavaScript while loop.

For example,

WHILE ( <condition> ) { DO | LOOP }
    <statement>;
    [ <statement>; ... ]
END { WHILE | LOOP } [ <label> ] ;

Please note that, the keyword DO should be paired with END WHILE and the keyword LOOP should be paired with END LOOP.

Snowflake Script Stored Procedure with WHILE Loop.

Following stored procedure demonstrate the use of a while loop.

create procedure example_while_Loop()
returns number(8, 0)
language sql
as
$$
declare
    counter number(8, 0);
    power_of_2 number(8, 0);
begin
    counter := 1;
    power_of_2 := 1;
    while (counter <= 8) do
        power_of_2 := power_of_2 * 2;
        counter := counter + 1;
    end while;
    return power_of_2;
end;
$$ ;

Snowflake Stored Procedures and User Defined Functions with FOR Loop

A FOR loop in Snowflake scripting repeats a sequence of steps a specific number of times. Snowflake Scripting supports the following two types of FOR loops:

Counter-Based FOR Loops
Cursor-Based FOR Loops

Counter-Based FOR Loops in Snowflake Scripting

A counter-based FOR loop is similar to what we have in other programming language that executes a specified number of times.

The syntax for a counter-based FOR loop is

FOR <counter_variable> IN [ REVERSE ] <start> TO <end> { DO | LOOP }
  <statement>;
  [ <statement>; ... ]
END { FOR | LOOP } [ <label> ] ;

Snowflake Script Stored Procedure with Counter-based FOR Loop

Following stored procedure demonstrate the use of counter-based for loop.

create or replace procedure example_for_Loop1()
returns number(8, 0)
language sql
as
$$
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;
$$;

Snowflake Script Anonymous Block with Counter-based FOR Loop

Following Snowflake Script Anonymous Block demonstrate the use of counter-based for loop.

execute immediate $$
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;
$$;

Cursor-Based FOR Loops in Snowflake Scripting

A cursor-based FOR loop iterates over a result set. The number of iterations is determined by the number of rows in the cursor.

The syntax for a cursor-based FOR loop is

FOR <row_variable> IN <cursor_name> DO
  <statement>;
[ <statement>; ... ]
END FOR [ <label> ] ;

Snowflake Script Stored Procedure with Cursor-based FOR Loop

Following stored procedure demonstrate the use of cursor-based for loop.

create procedure example_for_Loop2()
returns number(8, 0)
language sql
as
$$
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;
$$ ;

Snowflake Script Anonymous Block with cursor-based FOR Loop

Following Snowflake Script Anonymous Block demonstrate the use of cursor-based for loop.

execute immediate $$
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;
$$;

Snowflake Scripting REPEAT Loop

A REPEAT loop in Snowflake scripting iterates until a specified condition is true. In a REPEAT loop, Snowflake test the condition immediately after executing the body of the loop. As a result, the body of the loop always executes at least once.

The syntax for a REPEAT loop is

REPEAT
  <statement>;
  [ <statement>; ... ]
UNTIL ( <condition> )
END REPEAT [ <label> ] ;

Snowflake Script Stored Procedure with REPEAT Loop

Following stored procedure demonstrate the use of repeat loop.

create procedure example_repeat_Loop()
returns number(8, 0)
language sql
as
$$
begin
  let counter := 5;
  let number_of_iterations := 0;
  repeat
    counter := counter - 1;
    number_of_iterations := number_of_iterations + 1;
  until (counter = 0)
  end repeat;
  return number_of_iterations;
end;
$$;

Snowflake Script Anonymous Block with REPEAT Loop

Following Snowflake Script Anonymous Block demonstrate the use of REPEAT loop.

execute immediate $$
begin
  let counter := 5;
  let number_of_iterations := 0;
  repeat
    counter := counter - 1;
    number_of_iterations := number_of_iterations + 1;
  until (counter = 0)
  end repeat;
  return number_of_iterations;
end;
$$;

Snowflake Scripting LOOP Loop

A LOOP loop is special control structure that executes until a BREAK command is executed. The BREAK command is usually embedded inside branching logic such as IF or any CASE statements.

The syntax for a LOOP loop is,

LOOP
  <statement>;
  [ <statement>; ... ]
END LOOP [ <label> ] ;

Snowflake Script Stored Procedure with LOOP Loop

Following stored procedure demonstrate the use of LOOP loop.

create or replace procedure example_Loop()
returns number(8, 0)
language sql
as
$$
begin
  let counter := 5;
  loop
    if (counter = 0) then
      break;
    end if;
    counter := counter - 1;
  end loop;
  return counter;
end;
$$ ;

Snowflake Script Anonymous Block with LOOP Loop

Following Snowflake Script Anonymous Block demonstrate the use of LOOP loop.

execute immediate $$
begin
  let counter := 5;
  loop
    if (counter = 0) then
      break;
    end if;
    counter := counter - 1;
  end loop;
  return counter;
end;
$$

Snowflake Scripting Control Structures – IF, WHILE, FOR, REPEAT, LOOP