Learning Snowflake SQL and Scripting

Chapter 16. Building Stored Procedures

Now that you’ve learned the various components of the Snowflake Scripting language, it’s time to learn how to build stored procedures, which are compiled programs written using Snowflake Scripting and residing in Snowflake.

Why Use Stored Procedures?

There are a number of reasons you may want to use Snowflake Scripting to build stored procedures, such as:

• Scripts that are used repeatedly can be stored in the database for ease of use and sharing with others.
• Stored procedures allow for one or more parameter values to be passed in, enhancing the flexibility of your scripts.
• Stored procedure names can be overloaded, meaning that multiple stored procedures may have the same name as long as the parameter types differ.
• Stored procedures facilitate code reuse and hide complex logic from end users.

These are just a few of the advantages of stored procedures, and I’m sure you’ll discover more as you progress.

Turning a Script into a Stored Procedure

Let’s start with the final script from the previous chapter and walk through the steps needed to turn it into a stored procedure. Figure 16-1 shows the script.

Figure 16-1. Script to return average balance

This script divides the number of days in a month into the total balance and returns the average daily balance. Turning it into a stored procedure requires the following changes:

• Adding the create ...