Snowflake Scripting 101: Control Structures

In my other articles, we have talked about how to use basic blocks in Snowflake scripting , how to use Snowflake cursors and Snowflake scripting error handling. In this article, we will check Snowflake scripting control structures such as IF, WHILE, FOR, REPEAT and LOOP with some relevant examples.

Page Contents

• Introduction
• Fundamental Control Structures in Snowflake
• Snowflake Conditional control structures and Examples
  • IF Statement
  • Example: Snowflake Script Stored Procedure with IF-ELSE Block
• Snowflake Iterative control structures and Examples
  • WHILE loops in Snowflake
  • FOR loops in Snowflake
  • REPEAT in Snowflake
  • LOOP in Snowflake
• Other Snowflake Control Structures
  • CASE Expression
  • IFF Statement
• Conclusion

Introduction

Snowflake, a cloud-based data platform, offers powerful scripting capabilities for efficient data management and analysis. Snowflake supports SQL and JavaScript as programming languages for creating stored procedures and user-defined functions. Initially, only JavaScript was endorsed for creating stored procedures, but recent developments have introduced support for Snowflake script, closely resembling the PL/SQL programming language found in databases like Oracle and Teradata.

This expansion in Snowflake scripting capabilities offers robust control structures, including branching and looping, empowering users to effectively manipulate data. In this blog post, we’ll check Snowflake scripting control structures for data manipulation. We’ll start with a essential control structures like IF statements, loops, and switch statements.

Fundamental Control Structures in Snowflake

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 fundamental control structures:

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

Snowflake Conditional control structures and Examples

The Snowflake script accommodates the IF statement as a conditional control structure. This statement allows the execution of a specified set of statements if a given condition is satisfied.

IF Statement in Snowflake Script Stored Procedures and UDFs

If statements can execute specific tasks depending on certain conditions. You have the option to utilize three different forms of the IF statement.

• if
• if-else
• if-else-if

Basic if Statement in Snowflake Scripting

Utilize the basic if statement to designate a block of code for execution when a specified condition is true.

For example,

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

if-else Statement in Snowflake Scripting

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 Scripting

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

The following Snowflake script stored procedure illustrates the utilization of an if-else block statement.

create or replace procedure example_basic_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 Iterative Control Structures and Examples

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

Snowflake Script WHILE Loop

The while loop iterates through a block of code within a scripting block or stored procedure as long as a specified condition remains true. This loop can also be employed within an Anonymous Snowflake scripting block. The syntax for the WHILE loop closely resembles the while loop in JavaScript.

Snowflake Scripting WHILE Loop Syntax:

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

Structure:

• WHILE ( <condition> ): This part initiates the loop and specifies the condition that controls its continuation. As long as the condition remains true, the loop will keep executing.
• { DO | LOOP }: These keywords mark the beginning of the loop’s body, where the statements to be repeatedly executed reside. Both DO and LOOP have the same effect in this context.
• <statement>; : Individual statements within the loop’s body, each performing a specific task. Multiple statements can be included, separated by semicolons.
• END { WHILE | LOOP }: This keyword combo signals the end of the loop’s body, signifying where the code execution returns to the WHILE condition check.
• [ <label> ] ;: An optional label that can be assigned to the loop, enabling control flow statements like BREAK and CONTINUE to reference it explicitly.

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

Snowflake Scripting Anonymous Block with WHILE Loop

The following Snowflake anonymous block demonstrate the use of a while loop. In this example, the while loop is utilized to control the repetition of a specific set of statements within the block, continuing execution as long as a defined condition holds true.

DECLARE
    counter NUMBER(8, 0);      -- Loop counter.
    power_of_2 NUMBER(8, 0);   -- Stores the most recent power of 2 that we calculated.
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 Scripting Stored Procedure with WHILE Loop

Following stored procedure demonstrate the use of a while loop. In this example, the while loop is utilized to control the repetition of a specific set of statements within the Snowflake stored procedure, continuing execution as long as a defined condition holds true.

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 Scripting FOR Loop

A FOR loop in Snowflake scripting iterates through a sequence of steps for a predefined number of iterations. Snowflake Scripting offers support for two distinct 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> ] ;

Structure:

• FOR <counter_variable> IN: Initiates a counter-based loop, declaring a variable to track iterations.
• [ REVERSE ]: Optional keyword that reverses the iteration order, counting down from end to start.
• <start> TO <end>: Specifies the beginning and ending values for the counter.
• { DO | LOOP }: Marks the start of the loop’s body, where statements execute repeatedly.
• <statement>; [ <statement>; … ]: The statements to be executed within each iteration.
• END { FOR | LOOP }: Signals the end of the loop’s body.
• [ <label> ] ;: Optional label for the loop, used for control flow statements.

Snowflake Script Stored Procedure with Counter-based FOR Loop

Following stored procedure demonstrate the use of counter-based for loop. In this example, the for loop is utilized to control the repetition of a specific set of statements within the Snowflake stored procedure, continuing execution for predefined number of times.

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. In this example, the for loop is utilized to control the repetition of a specific set of statements within the Snowflake blocks, continuing execution for predefined number of times.

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> ] ;

Structure:

• FOR <row_variable> IN: Initiates a cursor-based loop, which iterates over rows in a result set.
• <cursor_name>: Specifies the name of the cursor that holds the result set to be processed.
• DO: Marks the beginning of the loop’s body, containing statements to be executed for each row.
• <statement>; [ <statement>; … ]: The statements to be executed for each row in the cursor.
• END FOR: Signals the end of the loop’s body.
• [ <label> ] ;: Optional label for the loop, used for control flow statements.

Snowflake Script Stored Procedure with Cursor-based FOR Loop

Following stored procedure demonstrate the use of cursor-based for loop. In this example, the for loop is utilized to control the repetition of a specific set of statements within the Snowflake blocks, continuing execution until records from Snowflake cursors are exhausted.

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. In this example, the for loop is utilized in Snowflake blocks to control the repetition of a specific set of statements within the Snowflake blocks, continuing execution until records from Snowflake cursors are exhausted.

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 becomes true. In a REPEAT loop, Snowflake tests the condition immediately after executing the body of the loop. Consequently, the body of the loop is guaranteed to execute at least once.

The syntax for a REPEAT loop is

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

Structure:

• REPEAT: Initiates the loop, indicating that the statements within its body will execute before the condition is checked.
• <statement>; [ <statement>; … ]: The statements to be executed repeatedly within the loop’s body.
• UNTIL ( <condition> ): Specifies the condition that, when met, will terminate the loop.
• END REPEAT: Signals the end of the loop’s body.
• [ <label> ] ;: Optional label for the loop, used for control flow statements.

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 a distinctive control structure in Snowflake scripting that continues execution until a BREAK command is encountered. Typically, the BREAK command is embedded within branching logic, such as an IF or CASE statement.

The syntax for a LOOP loop is,

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

Structure:

• LOOP: Initiates an infinite loop, meaning it will continue indefinitely unless explicitly terminated within its body.
• <statement>; [ <statement>; … ]: The statements to be executed repeatedly within the loop’s body.
• END LOOP: Signals the end of the loop’s body, but without any explicit condition check, the loop will immediately restart.
• [ <label> ] ;: Optional label for the loop, used for control flow statements.

Snowflake Script Stored Procedure with LOOP Loop

The 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

The following Snowflake scripting anonymous block demonstrate the use of LOOP loop.

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

Other Snowflake Control Structures

Snowflake supports additional conditional control statements that can be utilized in DML statements and scripting. CASE and IFF statements are examples of such conditional control statements.

• CASE Statement
• IFF Statement

Snowflake CASE Statement

A CASE statement is a conditional expression that allows for conditional logic within a query. It can be used to perform different actions based on specified conditions and is commonly used in SELECT, UPDATE, and WHERE clauses to provide flexibility in data manipulation and retrieval. It can be used in Snowflake stored procedures and anonymous block.

CASE Statement Syntax:

CASE ( <expression_to_match> )
    WHEN <expression> THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ ELSE
        <statement>;
        [ <statement>; ... ]
    ]
END [ CASE ] ;

Snowflake CASE Statement in Stored Procedure

The following stored procedure example demonstrates a simple CASE statement:

CREATE PROCEDURE case_demo_2(v VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    BEGIN
        CASE 
            WHEN v = 'first choice' THEN
                RETURN 'one';
            WHEN v = 'second choice' THEN
                RETURN 'two';
            ELSE
                RETURN 'unexpected choice';
       END CASE;
    END;
$$
;

Snowflake CASE Statement in Anonymous Block

The following anonymous block example demonstrates a simple CASE statement:

DECLARE 
    v VARCHAR := 'second choice';
BEGIN
        CASE 
            WHEN v = 'first choice' THEN
                RETURN 'one';
            WHEN v = 'second choice' THEN
                RETURN 'two';
            ELSE
                RETURN 'unexpected choice';
       END CASE;
END;

Snowflake IFF Statement

The IFF function in Snowflake is a simple, single-line expression used for conditional branching. It acts like a miniature “if-then-else” statement, returning one value if a condition is true and another value if it’s false.

IFF Statement Syntax:

IFF( <condition> , <expr1> , <expr2> )

Following is the simple IFF statement example.

select iff(True, 'true', 'false');

Conclusion

In conclusion, learning Snowflake Scripting’s control structures, including IF, CASE, loops, and cursors, allows you to automate recurring tasks and enhance data manipulation. It’s crucial to carefully evaluate conditions in loop constructs like WHILE, REPEAT, and LOOP to ensure proper loop termination.

Hope this helps 🙂