Writing Stored Procedure In Snowflake Scripting
Find Data Engineering Simplified Udemy Courses
Dive into the world of Snowflake SQL scripting as we guide you through a thorough tutorial packed with examples. Whether you’re a beginner or looking to enhance your skills, this playlist covers everything you need to know about creating and optimizing stored procedures in Snowflake. Join us on this educational journey to master SQL scripting in Snowflake, backed by real-world examples for a hands-on learning experience. Don’t miss out — unlock the power of Snowflake stored procedures today!
Ch-01: Power of Stored Procedure Using Snowflake Scripting
Dive into the world of Snowflake Scripting, where data developers can effortlessly write anonymous blocks or Stored Procedures to tackle a spectrum of use cases, from the straightforward to the very complicated ones.
Ch-02: Stored Procedure As Anti Pattern
It is important to understand if using stored procedure in Snowflake are Anti-Pattern or Core Design Pattern and that’s what we are going to learn in this tutorial. Unlock the world of stored procedures in data warehousing with our Snowflake Scripting tutorial! Discover common patterns, understand why some consider them bad practice, explore limitations, and dive into real-life Snowflake examples. Plus, get valuable tips for best practices in writing stored procedures with Snowflake scripting.
Ch-03: Block Structure
Block structures serve as containers for both Snowflake script and SQL scripting, providing a platform for encapsulation and execution. These structures can either be anonymous or attached to a stored procedure, and that’s precisely what we’ll delve into in this video. We’ll explore both the theoretical and practical aspects of block structures, demonstrating how to compose straightforward Snowflake scripts and troubleshoot them by intentionally introducing errors using our Snowsight WebUI.
Block Structure SQL Scripts (Ch-03)
Anonymous Block Using Snowflake SQL Scripting
-- DECLARE section allows us to declare variables
-- lets start with a variable without assiging any data type.
DECLARE
text_variable;
BEGIN
RETURN text_variable;
END;Same logic inside a stored procedure
-- Same logic inside a stored procedure
CREATE OR REPLACE PROCEDURE calculate_tax()
RETURNS FLOAT
LANGUAGE SQL
AS
declare
tax_amt number(10,2) DEFAULT 0.00
base_salary_slab number(10,2) DEFAULT 300000.00;
begin
-- gross salary and tax percentage value
LET gross_salary number(10,2) := 1000000.00;
LET tax_slab number(3,2) := 0.20;
-- taxable salaryto be calcualted
LET taxable_salary number(10,2) := 0.00;
-- salary calculation
IF (gross_salary > base_salary_slab) THEN
taxable_salary := (gross_salary-base_salary_slab);
tax_amt := taxable_salary * tax_slab;
END IF;
RETURN tax_amt;
end;Ch-04: Global Variables
When dealing with stored procedures and anonymous blocks in Snowflake, it’s crucial to grasp the methods for defining and declaring variables. Within the DECLARE section, various approaches exist for declaring variables in Snowflake, encompassing options such as specifying default values using expressions, built-in functions, and select statements.
This comprehensive video explores all these possibilities and elucidates each concept by demonstrating executions using the Snowsight WebUI.
Global Variables SQL Scripts (Ch-04)
-- DECLARE section allows us to declare variables
-- lets declare a variable with name but without assiging any data type
DECLARE
text_variable text;
BEGIN
RETURN text_variable;
END;
-- with procedure
CREATE OR REPLACE PROCEDURE my_variable_sp()
RETURNS FLOAT
LANGUAGE SQL
AS
DECLARE
text_variable text;
BEGIN
RETURN text_variable;
END;
-- call the stored procedure
call my_variable_sp();-- Variable with default expression
DECLARE
text_variable NUMBER default 10;
BEGIN
RETURN text_variable;
END;
-- with procedure
CREATE OR REPLACE PROCEDURE my_variable_sp()
RETURNS NUMBER
LANGUAGE SQL
AS
DECLARE
text_variable NUMBER default '<not-known>';
BEGIN
RETURN text_variable;
END;Ch-05: Local Variables
In this video tutorial, we will learn how to define local variables using let keyword in snowflake sql scripting while working with stored procedure as well as anonymous block. We will learn different way to define local variables and role of default as well as colon-equal operator to assign an expression to the local variable.
Local Variable SQL Scripts (Ch-05)
CREATE OR REPLACE PROCEDURE my_sp()
RETURNS text
LANGUAGE SQL
AS
DECLARE
global_var_01 text; -- initialized with null
global_var_02 DEFAULT 'global-value-02'; -- ininitialized with a default text and infer text data type
global_var_03 text DEFAULT 'global-value-03'; -- ininitialized with a text + text data type
BEGIN
LET local_var_01 text; -- without default value
LET local_var_02 DEFAULT 'local_default_var'; -- with default value
LET local_var_03 text DEFAULT 'local_default_var'; -- default + datatype
-- built-in function as default value
LET local_var_04 text DEFAULT current_role(); -- default + datatype
-- select statement as default value
LET local_var_05 number DEFAULT (select count(*) from my table ); -- default + select stmt
return local_var_01;
END;Ch-06:How To Use Variables Using Snowflake Scripting
Once variables are defined, it’s important to use them effectively. In this video, we’ll explore how to utilize variables in Snowflake SQL Scripting statements and standard SQL statements. We’ll cover using variables in standard expressions, including understanding data types. Additionally, we’ll delve into using the colon notation to access variables within SQL statements and explore the role of the identifier function. This tutorial aims to assist you in writing complex, dynamic, and efficient stored procedures using Snowflake scripting.
Colon Notation + Identifier SQL Scripts (Ch-06)
-- define a procedure
CREATE OR REPLACE PROCEDURE my_procedure()
RETURNS NUMBER(10,2)
LANGUAGE SQL
AS
DECLARE
-- 3 global variable defined
gross_salary number(10,2);
tax_percetage number(2,2) default 0.33;
taxable_salary number(10,2);
BEGIN
-- gross salary
gross_salary := 120000;
-- taxable salary calculation
taxable_salary := gross_salary * tax_percetage;
-- return the taxable salary
return taxable_salary;
END;Work with Text/String data type
CREATE OR REPLACE PROCEDURE delete_inactive_customer()
RETURNS text
LANGUAGE SQL
AS
DECLARE
-- 1 global variable defined
flag_val number(1) default -1;
BEGIN
LET sql_statement := 'delete from customer_table where inactive_flag = ' || flag_val;
execute immediate sql_statement;
-- return the taxable salary
return 'Inactive sustomers deleted successfully';
END;Colon Notation
-- lets create stored procedure
CREATE OR REPLACE PROCEDURE my_procedure()
RETURNS NUMBER(10,2)
LANGUAGE SQL
AS
DECLARE
-- 3 global variable defined
emp_id number(5) default 1000;
tax_percetage number(2,2) default 0.33;
taxable_salary number(10,2);
BEGIN
-- gross salary
LET gross_salary number(10,2) default (select gross_salary from employee_table where id = :emp_id );
-- taxable salary calculation
taxable_salary := gross_salary * tax_percetage;
-- return the taxable salary
return taxable_salary;
END;Identifier Function
-- lets create stored procedure
CREATE OR REPLACE PROCEDURE my_procedure()
RETURNS NUMBER(10,2)
LANGUAGE SQL
AS
DECLARE
-- 3 global variable defined
table_name text default 'employee_table';
pk_val number(5) default 1000;
tax_percetage number(2,2) default 0.33;
taxable_salary number(10,2);
BEGIN
-- gross salary
LET gross_salary number(10,2) default (select gross_salary from identifier(:table_name) where id = :pk_val );
-- taxable salary calculation
taxable_salary := gross_salary * tax_percetage;
-- return the taxable salary
return taxable_salary;
END;Ch-07: Assign Multiple Values Using INTO keyword
In certain situations, it becomes necessary to assign multiple values to multiple variables respectively. Snowflake scripting provides a specialized keyword to facilitate this process, which we will see and practice in this video tutorial. The focus will be on understanding and utilizing the INTO keyword for assigning values to variables, with emphasis on key considerations during stored procedure development. Furthermore, we will explore various error scenarios and debugging messages that may arise if the recommended approach is not adhered to while using the INTO keyword. This knowledge is intended to assist you in your development and debugging process while working with Snowflake SQL scripting.
Assign Multiple Values Using INTO keyword SQL Scripts (Ch-07)
CREATE OR REPLACE PROCEDURE my_procedure()
RETURNS NUMBER(10,2)
LANGUAGE SQL
AS
DECLARE
-- following works
min_balance number(10,2) default 0.00;
max_balance number(10,2) default 0.00;
BEGIN
-- using colon notation & into keyword
select min(c_acctbal),max(c_acctbal) into :min_balance, :max_balance from customer_tbl;
-- select min(c_acctbal),max(c_acctbal) into min_balance, max_balance from customer_tbl;
-- select c_acctbal,c_acctbal into :min_balance, :max_balance from snowflake_sample_data.tpch_sf1.customer limit 2;
-- select avg(c_acctbal),min(c_acctbal),max(c_acctbal) into :min_balance from snowflake_sample_data.tpch_sf1.customer;
-- return the variable
return max_balance;
END;Ch-08: Nested Block & Variable Visibility
The Snowflake Stored Procedure allows you to include nested or anonymous blocks within the body of your stored procedure. In certain situations, you may declare a variable with a different or identical name. It’s crucial to grasp how variable scope and visibility operate when dealing with nested or deeply nested blocks. This tutorial and hands-on guide will help you comprehend the workings of nested blocks and variable visibility in Snowflake.
Nested Block & Variable Scope SQL Scripts (Ch-08)
CREATE OR REPLACE PROCEDURE my_procedure()
RETURNS NUMBER(2,0)
LANGUAGE SQL
AS
DECLARE
-- global variable
global_var NUMBER(2,0) default 0;
BEGIN
LET outer_var NUMBER(2,0) := 10;
BEGIN
LET inner_var NUMBER(2,0) := 20;
-- all global & outer variable is accessible here.
global_var := inner_var + outer_var;
END;
-- the innver_var will not be accessible in this line.
-- return the taxable salary
return global_var;
END;Ch-09:
This tutorial video explains using the RETURN keyword in Snowflake scripting to send back a value, expression, or result set from a stored procedure. It begins by discussing the necessity of the return statement in a stored procedure, then covers topics like specifying return properties, using multiple return statements, and how data types are handled during implicit casting.
Upon completing the “ How To RETURN A Value/Expression/Table” chapter, you’ll be ready to answer the following questions:
- How to write a stored procedure that returns nothing?
- How to write a stored procedure that returns a result set?
- How to write a stored procedure that returns a table?
- It is possible to write a stored procedure that returns a non-null value?
- Is it possible to have multiple return statement from a stored procedure?
Ch-10:
Discover the ins and outs of IF-ELSE conditional logic in Snowflake SQL Scripting with this hands-on video tutorial. Learn about If-Else and If-ElseIf constructs, and how to use them in your stored procedures and anonymous block. Get the hang of nested IF statements and understand the importance of semicolons and brackets while working with IF-ELSE conditional logic.
Upon completing the “Exploring IF-ELSE Conditional Logic in Depth” you’ll be ready to answer the following questions:
- How to apply and use conditional logic a SQL based stored procedure?
- How to debug and understand the syntax error message while working with SQL based stored procedure?
- How to write nested IF-ELSEIF-ELSE-IF conditional logic?
- How the date and boolean data type works in conditional expression?
- How to use the AND/OR operator while working with IF-ELSE condition?
Ch-11: CASE/WHEN Statement
Discover everything about CASE/WHEN conditional logic in Snowflake SQL Scripting with this hands-on video tutorial. Learn about simple and searched CASE/WHEN constructs, and how to use them in your stored procedures and anonymous block.
Get the hang of nested CASE/WHEN statements and understand the importance of semicolons and brackets while working with CASE/WHEN conditional logic.
On completion, you’ll be ready to answer the following questions:
- How to apply and use CASE/WHEN conditional logic a SQL based stored procedure?
- How to debug and understand the syntax error message while working with SQL based stored procedure?
- Difference between simple/searched CASE/WHEN conditional logic?
- How to write nested blocked within CASE/WHEN conditional logic?
- How to use the AND/OR operator while working with CASE/WHEN condition?
Ch-12: Working With FOR Loops
In this snowflake scripting developer’s guide, you would learn about FOR loops and how it has to be used while working with stored procedure as well as anonymous block.
Upon completing the “Working With FOR Loops “ chapter-12, you’ll be ready to answer the following questions:
- How to use FOR loop in a stored procedure?
- What are the two type of for loops in snowflake scripting?
- What are the limitations of for loops in snowflake scripting?
Snowflake Scripting FOR Loop Examples
-- Anonymous Block
DECLARE
start_at INTEGER DEFAULT 1; -- Counter starts at
end_at INTEGER DEFAULT 20; -- Counter ends at
return_msg TEXT DEFAULT ''; -- Return variable
BEGIN
FOR i IN start_at TO end_at DO
return_msg := return_msg || i || ' ';
END FOR;
RETURN return_msg;
END;
-- with stored procedure
CREATE OR REPLACE PROCEDURE print_numbers_sp(end_at INTEGER)
RETURNS TEXT
LANGUAGE SQL
AS
DECLARE
start_at INTEGER DEFAULT 1; -- Counter starts at
--end_at INTEGER DEFAULT 20; -- Counter ends at
return_msg TEXT DEFAULT ''; -- Return variable
BEGIN
FOR i IN start_at TO end_at DO
return_msg := return_msg || i || ' ';
END FOR;
RETURN return_msg;
END;
call print_numbers_sp(10);FOR Loop — DO vs. LOOP
DECLARE
start_at INTEGER DEFAULT 1; -- Counter starts at
end_at INTEGER DEFAULT 20; -- Counter ends at
return_msg TEXT DEFAULT ''; -- Return variable
BEGIN
FOR i IN start_at TO end_at LOOP
return_msg := return_msg || i || ' ';
END FOR;
RETURN return_msg;
END;Use of REVERSE Keyword
DECLARE
start_at INTEGER DEFAULT 1; -- Counter starts at
end_at INTEGER DEFAULT 20; -- Counter ends at
return_msg TEXT DEFAULT ''; -- Return variable
BEGIN
FOR i IN REVERSE start_at TO end_at DO
return_msg := return_msg || i || ' ';
END FOR;
RETURN return_msg;
END;FOR Loop & Continue/Break Example
DECLARE
start_at INTEGER DEFAULT 1; -- Counter starts at
end_at INTEGER DEFAULT 20; -- Counter ends at
return_msg TEXT DEFAULT ''; -- Return variable
BEGIN
FOR i IN start_at TO end_at DO
return_msg := return_msg || i || ' ';
if (i > 15) then
break;
end if;
END FOR;
RETURN return_msg;
END;Use of Labels in FOR loops
CREATE OR REPLACE PROCEDURE loop_with_lable_sp()
RETURNS TEXT
LANGUAGE SQL
AS
DECLARE
start_at INTEGER DEFAULT 1; -- Counter starts at
end_at INTEGER DEFAULT 5; -- Counter ends at
return_msg TEXT DEFAULT ''; -- Return variable
BEGIN
-- loop-1
FOR i IN start_at TO end_at DO
return_msg := return_msg || i || ' (';
-- loop for odd
FOR j IN start_at TO end_at DO
IF (j = 1 AND i = 3) THEN
break outer_loop;
END IF;
return_msg := return_msg || i*j || ' ';
END FOR inner_loop; --end loop
return_msg := return_msg || ') ';
END FOR outer_loop;
RETURN return_msg;
END;
call loop_with_lable_sp();Cursor Based FOR Loop
DECLARE
return_msg TEXT DEFAULT ''; -- Return variable
BEGIN
-- creat a table + insert record
execute immediate 'create or replace temp table sales (order_id int,order_value int)';
execute immediate 'insert into sales values (1,100),(2,90),(3,287),(4,76),(5,99) ';
let sales_cursor CURSOR FOR select order_id, order_value from sales;
let total_sales int :=0;
FOR record IN sales_cursor DO
total_sales := total_sales + record.order_value;
END FOR total_sales;
return_msg := 'Total Sales ' || total_sales;
RETURN return_msg;
END;Ch-18: Complete Exception Handling Guide
In this Snowflake SQL scripting developer’s guide, you would learn about EXCEPTIONAL HANDLING techniques and how it has to be used while working with stored procedure as well as anonymous block.
Upon completing the “Complete Exception Handling Guide “ chapter-18, you’ll be ready to answer the following questions:
- How EXCEPTIONs are raised in a stored procedure?
- What is built-in and custom EXCEPTIONs in snowflake stored procedure?
- How to handle built-in exceptions?
- How to handle custom exceptions?
Implicit Snowflake Exception Types
-- part-1
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE DEMO.PUBLIC.TABLE_DOES_NOT_EXIST';
return 'Statement Executed';
END;
-- part-2
BEGIN
--EXECUTE IMMEDIATE 'SELECT * FROM DEMO.PUBLIC.TABLE_DOES_NOT_EXIST';
return 'Statement Executed';
END;
-- part-3
BEGIN
--LET total_emp number default (select count(*) from employee);
LET total_emp number default (select count(*)/0 from employee);
return total_emp;
END;How To Handle Exception
DECLARE
total_emp text default 0;
BEGIN
-- following line will raise an exception as table does not exist
-- it will raise (or so called throw)STATEMENT_ERROR
EXECUTE IMMEDIATE 'DROP TABLE DEMO.PUBLIC.EMP_TABLE';
EXCEPTION
WHEN EXPRESSION_ERROR THEN
RETURN -1;
WHEN STATEMENT_ERROR THEN
RETURN -2;
WHEN OTHER THEN
RETURN total_emp;
return total_emp;
END;Implicit Exception Object
CREATE OR REPLACE TABLE AUDIT_TABLE(MSG text);
DECLARE
my_custom_exception EXCEPTION (-20001, 'Customer error msg using custom exception');
BEGIN
LET audit_msg text default '';
RAISE STATEMENT_ERROR; -- use raise keyword to raise or throw an exception
RETURN 'Before exception block'; -- this line will never be executed
-- handler section
EXCEPTION
audit_msg := 'SQLCODE = ' ||SQLCODE || ', SQLERRM =' || SQLERRM || ', SQLSTATE = ' || SQLSTATE;
WHEN EXPRESSION_ERROR or STATEMENT_ERROR THEN
audit_msg := 'SQLCODE = ' ||SQLCODE || ', SQLERRM =' || SQLERRM || ', SQLSTATE = ' || SQLSTATE;
INSERT INTO AUDIT_TABLE (MSG) VALUES (:AUDIT_MSG);
WHEN my_custom_exception THEN
audit_msg := 'SQLCODE = ' ||SQLCODE || ', SQLERRM =' || SQLERRM || ', SQLSTATE = ' || SQLSTATE;
INSERT INTO AUDIT_TABLE (MSG) VALUES (:AUDIT_MSG);
WHEN OTHER THEN
audit_msg := 'SQLCODE = ' ||SQLCODE || ', SQLERRM =' || SQLERRM || ', SQLSTATE = ' || SQLSTATE;
INSERT INTO AUDIT_TABLE (MSG) VALUES (:AUDIT_MSG);
-- No executable statement after exception section
END;