Snowflake Scripting - Writing Stored Procedures Using SQL

Understand stored procedures as a set of statements executed in order to run multiple SQL commands, with dynamic parameters, conditional logic, and loops, in Snowflake scripting.

Learn how to create databases and schemas and define sample stored procedures in Snowflake scripting, including parameters, returns, and calling procedures. See square root examples for basic execution.

Learn how to declare and assign variables in snowflake scripting, using declaration and body sections, defaults, lit, and binding with colon or dollar for session variables, testing with execute immediate.

Demonstrate variables in Snowflake scripting via declare and assign, use execute immediate, and derive full names by concatenating first, middle, and last names; explore session variable scope and simple arithmetic.

Explore table literals and identifiers in Snowflake scripting, using table of with scalar arguments for dynamic table names in the from clause, and identifiers for referencing any database objects.

Demonstrates writing snowflake scripting stored procedures with table literals and identifiers, using session variables and a parameter to count rows in employees, departments, countries, and locations.

Explore flow control in snowflake scripting by mastering branching constructs, including if-else and case statements, with syntax rules, conditions, and how true conditions guide the execution flow.

Practice if and case statements in Snowflake scripting by building small examples that determine even or odd numbers and compute taxes via slabs using session variables and execute immediate.

Master looping in snowflake scripting with for loops (counter and cursor based), while, repeat, and loop, using break and syntax to repeat blocks and process rows in a cursor.

Master for and while loops in snowflake scripting with practical examples: print a star triangle and identify primes up to n; return results as a stored procedure table.

Cursors enable row-by-row processing of rows from tables or views by declaring, opening, fetching, and closing a cursor, with dynamic runtime parameters and bind variables.

Learn how to group statements into transactions in Snowflake using begin transaction, commit, and rollback; understand autocommit, scope, and how DDL inside a transaction is treated.

Explore how Snowflake scripting uses stored procedures to manage transactions with begin transaction, commit, and rollback, and how exception handling prevents partial commits.

Explore generic procedures in Snowflake scripting to automate data loads, implement SCD type 1 and 2, automate 1-to-1 view creation, and migrate DDL across databases.

Create a single Snowflake stored procedure to automate loading multiple file types from any cloud, using a control table, a temporary stage, and a single copy command.