Snowflake Control Structures

The best part about Snowflake is it supports JavaScript as a programming language to write stored procedures and user defined functions. The Stored procedure uses JavaScript to combine SQL with control structures such as branching and looping. In this article, we will check Snowflake branching and looping control structures.

You can use two types of control structures inside stored procedures and user defined functions.

Following are the Snowflake control structures

Branching Structures – Sometimes called conditional control structures
Looping Structures – Sometimes called Iterative control structures.

Branching Structures – Snowflake conditional control structures

There are two types of conditional control structures available in Java Script.

Following are two branching structures that you can use in Snowflake stored procedures.

if
switch

IF Statement in Snowflake Stored Procedures and UDFs

The if statements can perform a particular task based on the certain conditions.

There are four forms of IF statement that you can use. For example,

if
if-else
if-else-if

Simple if Statement in Snowflake

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

For example,

if (condition) {
  //  block of code to be executed if the condition is true
}

if-else Statement in Snowflake

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

For example,

if (condition) {
  //  block of code to be executed if the condition is true
} else {
  //  block of code to be executed if the condition is false
}

if-else-if Statement in Snowflake

Use the else if statement to specify a new condition in else block.

For example,

if (condition1) {
  //  block of code to be executed if condition1 is true
} else if (condition2) {
  //  block of code to be executed if the condition1 is false and condition2 is true
} else {
  //  block of code to be executed if the condition1 is false and condition2 is false
}

Snowflake Stored Procedure with if else Block

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

create or replace procedure proc_if_else_test(n1 float, n2 float)
 returns float not null
 language javascript
 as 
 $$
	if (N1>N2) { return N1 } else { return N2}
 $$
 ;

Switch Statement in Snowflake Stored Procedures and User Defined Functions

You can use the switch statement to select one of many code blocks to be executed inside stored procedure and user defined function.

For example,

switch(expression) {
  case x:
    // code block
    break;
  case y:
    // code block
    break;
  default:
    // code block
}

Switch statement in Snowflake Stored Procedure Example

Following example demonstrates the usage of switch statement within stored procedures.

create or replace procedure proc_switch_test()
 returns varchar not null
 language javascript
 as 
 $$
	switch (new Date().getDay()) {
	  case 0:
		return "Sunday";
		break;
	  case 1:
		return "Monday";
		break;
	  case 2:
		 return "Tuesday";
		break;
	  case 3:
		return "Wednesday";
		break;
	  case 4:
		return "Thursday";
		break;
	  case 5:
		return "Friday";
		break;
	  case 6:
		return "Saturday";
	}
 $$
 ;

Looping Structures – Snowflake Iterative control structures

With the help of iterative control statements, you can control the flow of execution in your stored procedures and user defined functions.

Snowflake supports following iterative control structures:

while
do .. while
for

While loop Inside 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.

For example,

while (condition) {
  // code block to be executed
}

while loop in Snowflake Stored Procedure example

Following stored procedure demonstrate the use of while loop.

create or replace procedure proc_while_test()
 returns float not null
 language javascript
 as 
 $$
	var total = 0
	var i = 0
	while (i < 10) {
	  total = total + i	
	  i++;
	}
	return total
 $$
 ;

do/while loop Inside Stored Procedures and User Defined Functions

In the case of do..while, loop will execute the code block once, before checking if the condition is true, then it will repeat the loop as long as the condition is true.

For example,

do {
  // code block to be executed
}
while (condition);

do/while loop in Snowflake Stored Procedure example

Following stored procedure demonstrate the use of do..while loop.

create or replace procedure proc_do_while_test()
 returns float not null
 language javascript
 as 
 $$
	var total = 0
	var i = 0
	do {
	  total = total + i	
	  i++;
	} while (i < 10) 
	return total
 $$
 ;

for loop Inside Stored Procedures and User Defined Functions

for loops through a block of code inside a stored procedure or user defined function predefined number of times.

For example,

for (statement 1; statement 2; statement 3) {
  // code block to be executed
}

for loop in Snowflake Stored Procedure example

Following stored procedure demonstrate the use of for loop.

create or replace procedure proc_for_test()
 returns float not null
 language javascript
 as 
 $$
	var total = 0
	var i = 0
	for ( i = 0; i < 10; i ++)
	{
	  total = total + i	
	  i++;
	}
	return total
 $$
 ;

There are many forms of for loops available in JavaScript. For example, for/in, for/of. You can use all those in your stored procedures and user defined functions.