What You Need to Know About Scripting in SQL with Snowflake

Snowflake Scripting is now generally available, allowing you to create scripts and stored procedures in SQL with Snowflake. This procedural language extends the SQL language with structures and control statements, such as conditional and loop statements, and makes it easy for Snowflake users (many of whom know and love SQL) to create stored procedures and translate existing SQL-based scripts.

During the preview, we’ve seen incredible adoption and usage of this feature. It’s easy to get started with Snowflake Scripting. In the tips below, we’ll run through some important concepts to know, and you’ll be off and running in no time. 

With this feature, you write your procedural code within a Snowflake Scripting block. You can use a block in the definition of a stored procedure, or, if you don’t want to use it in a stored procedure, you can create an anonymous block as a separate, standalone SQL statement. 

One key new functionality is the ability to return a table with Snowflake Scripting. This way, Snowflake users can not only use Snowflake Scripting to develop their ETL and data processing tasks, but it also simplifies the development or migrations of reporting procedures. Snowflake Scripting is deeply integrated with Snowflake’s SQL and can also be used and shared when building your data sharing or marketplace applications.

Snowflake Scripting supports branching constructs including IF and CASE statements. 

• IF enables you to execute a set of statements if a condition is met. 
• CASE works similarly to IF but is simpler when specifying multiple conditions.  

There are four types of loops: FOR, WHILE, REPEAT, and LOOP. 

• FOR loops repeat a sequence of steps for a specified number of times or for each row in a result set. They can be either counter-based or cursor-based. 
• WHILE loops only operate while a condition is true. The WHILE condition is tested immediately before the body of the loop and if the condition is false, the loop won’t execute (even once if it happens on the first iteration). 
• REPEAT loops will continue until a condition is true. 
• LOOP loops will keep executing until a command is given. 

You can use a cursor to iterate through query results one row at a time. To retrieve data from the results of a query, use a cursor. You can use a cursor in loops to iterate over the rows in the results.

Within a Snowflake Scripting block, you can raise an exception if an error occurs, and that exception will prevent the next lines of code from executing. You can also handle exceptions that occur in your Snowflake Scripting code. 

When an exception is raised, Snowflake Scripting attempts to find a handler. An exception handler can contain its own exception handler in case an exception occurs while handling another exception.

Visit Snowflake documentation to learn more about Snowflake Scripting, and check out some sample SQL scripts on Medium to get you started.

BONUS: Rajiv Gupta, a Snowflake data superhero, also has a series on Medium that includes more on branching constructs, loops, cursor results, and exception handling.