Are you looking to find what are the different control flow structure we can have in the snowflake cloud data warehouse or maybe you are looking for a solution about how to use the if condition within the Snowflake using the stored procedure. Or may be you wanted to learn how to use the while, for or do while loop in the Snowflake. After going through this article you will be able to understand and operate fully around the different types of the control flow structures like if else conditions, while loop, do while loop and switch case. I will also share my expertise and experience as well about control structures and various challenges which you can encounter while using it. Let’s don’t waste the time and I will take you quickly to the code directly where I will show you the operational details of the Snowflake.
Date Time conversion in Snowflake
Contents
- 1 What are the control structures in the Snowflake?
- 2 How to use If condition in Snowflake
- 3 How to use If else condition in Snowflake
- 4 How to use If else-if else condition in Snowflake
- 5 How to use Switch Statement in Snowflake Stored Procedures and User Defined Functions
- 6 Looping Structures – Snowflake Iterative control structures
- 7 How to use the while loop in Snowflake stored procedure and User defined functions (UDF) ?
- 8 How to use the Do while loop in Snowflake stored procedure and User defined functions (UDF) ?
- 9 How to use the for loop in Snowflake stored procedure and User defined functions (UDF) ?
- 10 Final Thoughts
What are the control structures in the Snowflake?
Control structure is the structure which defines how the control move from one line to next line within the Snowflake code. As the Snowflake allows to use the JavaScript with the stored procedure along with the SQL hence all the control flow structure capability of the JavaScript becomes available to the Snowflake stored procedure.
There are three different types of control flow structure in Snowflake :
- Sequential flow
- Conditional flow (include if else, swtich)
- Loops (include while, do while , for loop)
Let’s understand the Condition Flow in more details now.
How to use If condition in Snowflake
As we talked earlier, stored procedure uses the java script hence we can have if condition within the Snowflake stored procedure quite possible.
if ( condition) {
Steps to perform once the condition become successfully true.
}
Use case Scenario for If Condition
For example in the stored procedure which calculate the discount on the item only if the price of the item is more than 100.
Example of If condition in Snowflake Stored Procedure
create or replace procedure check_discount_price (price float)
returns float not null
language javascript
as
$$
var discount =0;
if (price >100) {
discount = price*0.10;
}
return discount;
$$
;
How to use If else condition in Snowflake
As we talked earlier, stored procedure uses the java script hence we can have if else condition within the Snowflake stored procedure quite possible.
if ( condition) {
Steps to perform once the condition become true.
} else {
Steps to perform once the condition become false.
}
Use case Scenario for If else Condition
For example in the stored procedure which give 10% discount on the item only if the price of the item is more than 100 otherwise 5%.
Example of If else condition in Snowflake Stored Procedure
create or replace procedure check_discount_price (price float)
returns float not null
language javascript
as
$$
var discount =0;
if (price >100) {
discount = price*0.10;
}
else {
discount = price*0.05;
}
return discount;
$$
;
How to use If else-if else condition in Snowflake
As we talked earlier, stored procedure uses the java script hence we can have if else condition within the Snowflake stored procedure quite possible.
if ( condition 1) {
Steps to perform once the condition 1 become true.
} else if ( condition 2) {
Steps to perform once the condition 2 become true.
} else {
Steps to perform once the none of the condition become true.
}
Use case Scenario for If else Condition
For example in the stored procedure which give 10% discount on the item only if the price of the item is more than 100, 15% if price is above 500 otherwise flat 5%.
Example of If else-if else condition in Snowflake Stored Procedure
create or replace procedure check_discount_price (price float)
returns float not null
language javascript
as
$$
var discount =0;
if (price >100 & price <=500) {
discount = price*0.10;
}
else if (price >500 ) {
discount = price*0.15;
}
else {
discount = price*0.05;
}
return discount;
$$
;
How to use Switch Statement in Snowflake Stored Procedures and User Defined Functions
Switch statement is almost same like using the if else-if else block. Using the switch statement we can create some kind of menu based algorithm. It is better choice over the if else if you feel that your condition involves the exact matching rather than range based.
switch ( expression) {
case A:
steps to execute
break;
case B:
steps to execute
break;
Default:
steps to execute
break;
Use case Scenario for If else Condition
For example in the stored procedure which give different discount based on the different item code of the product
Example of If condition in Snowflake Stored Procedure
create or replace procedure check_discount_price (item_code int price float)
returns float not null
language javascript
as
$$
var discount =0;
switch (item_code) {
case 101:
discount =0.05;
break;
case 102:
discount =0.1;
break;
case 103:
discount =0.15;
break;
case 104:
discount =0.2;
break;
default:
discount =0.25;
break;
}
return discount*price;
$$
;
Looping Structures – Snowflake Iterative control structures
Looping or the interactive control flow structure is one of the popular control flow structure needed for doing any iterative task. We have three different types of the loops availale to us in which are as follows:
- while loop
- Do while loop
- For loop
How to use the while loop in Snowflake stored procedure and User defined functions (UDF) ?
While loop statement used when only wanted to run the loop if the specified condition is true. Here in the while loop first the condition get checked then only control goes inside the while loop. You also need to take care of updating the variable so that at some point condition should becomes false and your loop get ended.
while( condition) {
// Step you want to get executed once condition become true
// Increment or decrement the counter
}
Use case Scenario for While loop
For example, in the stored procedure, which calculate the sum of ten numbers
Example of while loop in Snowflake Stored Procedure
create or replace procedure while_loop_example(item_code int price float)
returns float not null
language javascript
as
$$
var num =0;
var sum =0;
while(num<10) {
sum = sum +num;
num= num+1;
}
return sum
$$
;
How to use the Do while loop in Snowflake stored procedure and User defined functions (UDF) ?
Do while loop statement used when you wanted to run the loop at least once even if the specified condition is false. Here in the do while loop first the loop get executed and then condition get checked. You also need to take care of updating the variable so that at some point condition should becomes false and your loop get ended.
do( ) {
// Step you want to get executed once condition become true
// Increment or decrement the counter
} while (condition)
Use case Scenario for Do While loop
For example, in the stored procedure, which calculate the sum of ten numbers
Example of do while loop in Snowflake Stored Procedure
create or replace procedure do_while_loop_example(item_code int price float)
returns float not null
language javascript
as
$$
var num =0;
var sum =0;
do {
sum = sum +num;
num= num+1;
}while (num<10)
return sum
$$
;
How to use the for loop in Snowflake stored procedure and User defined functions (UDF) ?
For loop statement used when you wanted to run couple of statement iteratively until and unless certain condition is true. Once the specified condition becomes false then only loop will get stopped. Here in the for loop first the condition get evaluated then control goes inside the loop get executed. You also need to take care of updating the variable so that at some point condition should becomes false and your loop get ended.
for(declaration;condition;increment/decrement) {
// Step you want to get executed once condition become true
}
Use case Scenario for for loop
For example, in the stored procedure, which calculate the sum of ten numbers
Example of for loop in Snowflake Stored Procedure
create or replace procedure forloop_example(item_code int price float)
returns float not null
language javascript
as
$$
var num =0;
var sum =0;
for (var num =0; num<10; num++) {
sum = sum +num;
}
return sum
$$
;
Snowflake official Documentation Link
Final Thoughts
By this, we have reached the last section of the blog. In this blog, we have learned what are the different control structure we have in the Snowflake. How we use the if condition within the stored procedure of the Snowflake. I have also explained how to use the for loop and switch case within the Snowflake. We have also discussed different example of if else, while, do while and for loop within the Snowflake using the JavaScript.
Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.
Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.