I’ve been primarily a SQL Server developer for the majority of my career, but along the way have needed to use a variety of database technologies including Oracle, Vertica, Teradata, PostgreSQL, and several others. A common behavior between them all is the ability to write some flavor of a stored procedure to encapsulate a block of reusable code efficiently. Snowflake is no different, but looking at the stored procedure documentation for Snowflake, stored procedures supported in Snowflake Scripting (SQL), and several other language options including Java and Python.
Why Use Stored Procedures?
Creating a stored procedure allows for developers to reuse code that ranges from very simple to complex and encapsulates any business logic into a single method for processing. Over my career, I’ve seen stored procedures range from simple record insertion procedures to those that load multiple data tables with dynamic code using a variety of metadata tables. The possibilities are endless.
I was working on a Snowflake proof of concept recently and even with all the robust logging, query profiles, and other information provided by Snowflake “out-of-the-box”, I like having an event log table to write custom events that I care about. I track items such as when a procedure starts and ends, when each step in the procedure starts and ends, and record counts for intermediate steps and errors.
As I was looking to re-use some SQL Server code, I found that the task of migrating from SQL Server to Snowflake was not going to be a copy and paste exercise, but rather one where I needed to re-write the code to use Snowflake methods, but still get the desired output. The first hurdle I ran into was the syntax of the procedure itself, but a second and unexpected hurdle was that Snowflake does not support default parameter values for stored procedures. Many of my “standard” procedures have parameters with defaults defined to allow “shortcutting” the proc call in a script or from another procedure. I’ll walk through how I create a simple event log table process in both SQL Server and Snowflake to show the differences in the procedures.
Creating an Event Log
In the real-world use case I described above, my standard “logging” procedure in SQL Server has several inputs with default values set and those are only changed to non-default values when writing an error or some other “non-standard” informational event. In the next few steps, I’ll walk through how I set up the table and procedure in SQL Server and Snowflake, highlighting the differences.
Event Log Table Setup
To show the differences in behavior between SQL Server and Snowflake, I first need the event log table to build the stored procedure against. The table syntax between SQL Server and Snowflake is slightly different, so I will need two scripts to set up our sample tables. Note: this post assumes access to both a SQL Server and Snowflake. Snowflake offers a 30-day trial account if needed. These demos are based on SQL Server 2019 Enterprise and Snowflake Enterprise.
/*SQL SERVER*/
CREATE TABLE dbo.tbl_evt_log(
evt_id INT IDENTITY(1,1)
,evt_type VARCHAR(10)
,evt_desc VARCHAR(1000)
,evt_timestamp DATETIME DEFAULT GETDATE()
,evt_username VARCHAR(50) DEFAULT SUSER_SNAME()
)
/*SNOWFLAKE*/
CREATE TABLE tbl_evt_log(
evt_id INT AUTOINCREMENT START WITH 1 INCREMENT BY 1
,evt_type VARCHAR(10)
,evt_desc VARCHAR(1000)
,evt_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP()
,evt_username VARCHAR(50) DEFAULT CURRENT_USER()
)
/*BOTH SQL SERVER AND SNOWFLAKE - INSERT SAMPLE ROWS*/
INSERT INTO tbl_evt_log(evt_type,evt_desc)
VALUES
('INFO','First Event Log Entry'),
('ERROR','First Error Entry')
SQL Server Stored Procedure
Next, I’ll set up a simple SQL Stored procedure for writing to the event log table. Since the target table has an identity column setup for the event ID, and defaults for timestamp and username, the stored procedure only needs to specify the event type and event description. In “normal” use, the log entries are informational, so by defaulting the event type to “INFO” as part of our procedure definition, it can save a step or two when calling the proc.
/*STORED PROCEDURE CREATION SCRIPT*/
CREATE PROCEDURE dbo.usp_evt_log
(
@evt_type VARCHAR(10) = 'INFO',
@evt_desc VARCHAR(1000)
)
AS
INSERT INTO dbo.tbl_evt_log
(evt_type,evt_desc)
VALUES
(@evt_type,@evt_desc)
/*SAMPLE CALLS*/
EXEC dbo.usp_evt_log @evt_desc = 'Event written from proc'
EXEC dbo.usp_evt_log @evt_type = 'ERROR',@evt_desc = 'Error written from proc'
/*EVENT TABLE*/
SELECT * FROM tbl_evt_log;
/*
evt_id evt_type evt_desc evt_timestamp evt_username
----------- ---------- ------------------------------------------------ ------------
1 INFO First Event Log Entry 2023-10-02 04:44:31.820 eheilman
2 ERROR First Error Entry 2023-10-02 04:44:31.820 eheilman
3 INFO Event written from proc 2023-10-02 04:54:26.317 eheilman
4 ERROR Error written from proc 2023-10-02 04:54:26.320 eheilman
*/
In the code block above, notice in the sample calls to the procedure the @evt_type parameter has to be set only when a non-default value is needed. SQL Server allows for parameters to have default values; however, Snowflake does not which will be shown in the next step.
Snowflake Procedure Attempt 1
Naturally, I assumed when first starting to work with Snowflake that most of my SQL Server code and methods would naturally translate. I found a good chunk of it did, but procedure defaults did not! The syntax for Snowflake procedures is quite different from SQL Server in which a return value of some sort must be specified as well as referencing input parameters is quite different. My first attempt at the code to replicate my SQL Server procedure in Snowflake looked something like this (note parameters are referenced with a “:” in Snowflake procedures rather than “@” like SQL Server:
CREATE OR REPLACE PROCEDURE USP_EVT_LOG(EVT_TYPE VARCHAR(10) = 'INFO',EVT_DESC VARCHAR(1000))
AS
BEGIN
INSERT INTO TBL_EVT_LOG(EVT_TYPE,EVT_DESC)
VALUES(:EVT_TYPE,:EVT_DESC);
END;
Which produces this error:
Hmmm…perhaps I need to replace the “=” with “DEFAULT”. That works for Snowflake tables….
Okay…now this is getting frustrating. I will try taking out the default value entirely.
CREATE OR REPLACE PROCEDURE USP_EVT_LOG(EVT_TYPE VARCHAR(10),EVT_DESC VARCHAR(1000))
AS
BEGIN
INSERT INTO TBL_EVT_LOG(EVT_TYPE,EVT_DESC)
VALUES(:EVT_TYPE,:EVT_DESC);
END;
I’m missing something here, this is not going to be a copy/paste exercise to convert the SQL Stored Proc to a Snowflake procedure. Time to hit the documentation to figure out what I’m doing wrong.
Snowflake Procedure Attempt 2
After consulting the documentation, I found that a return type is needed in the definition and the procedure needs to return a value of that datatype. Additionally, I found there is no way to specify a default value for a parameter; but a parameter can be passed a NULL value. I’ll try that method for the evt_type parameter and use code in the proc to set the default. Not quite as simple as SQL Server, but manageable. I plan to return a BOOLEAN of TRUE if the procedure works and use COALESCE to get around the default parameter issue.
/*CREATE PROC*/
CREATE OR REPLACE PROCEDURE USP_EVT_LOG(EVT_TYPE VARCHAR(10),EVT_DESC VARCHAR(1000))
RETURNS BOOLEAN
AS
BEGIN
INSERT INTO TBL_EVT_LOG(EVT_TYPE,EVT_DESC)
VALUES(COALESCE(:EVT_TYPE,'INFO'),:EVT_DESC);
RETURN TRUE;
END;
/*SAMPLE CALLS*/
CALL USP_EVT_LOG(NULL,'Event written from proc');
CALL USP_EVT_LOG('ERROR','Error written from proc');
Notice in the sample calls above, I have to pass a NULL to the first parameter if I want to use the default value of “INFO”. Not necessarily the end of the world, but just one more thing to remember that’s different from SQL Server. Also, notice that the parameters were passed positionally rather than by name. Like SQL Server, Snowflake procedures can be called with named inputs. Perhaps, I can just not use EVT_TYPE?
/*TEST 1 - NAME BOTH*/
CALL USP_EVT_LOG(
EVT_TYPE => NULL,
EVT_DESC => 'TEST'
);
/*WORKS!*/
/*TEST 2 - SKIP EVT_TYPE*/
CALL USP_EVT_LOG(
EVT_DESC => 'TEST'
);
/*ERROR!*/
This is interesting and it sounds like I can build a procedure of the same name with a different signature in Snowflake, which is something not supported in SQL Server. After consulting documentation and other resources, it does appear that Snowflake supports polymorphism or overloading, and procedures can be created with the same name with a different signature. I’ll have to check that out for a future post!
Speed Bumps
Stored procedures are fully supported by Snowflake; however, when migrating data processes from SQL Server to Snowflake, it won’t be a copy/paste exercise. Below are some of the “gotchas” I ran into in this exercise.
Default Parameter Values
SQL Server supports setting default parameter values as part of the procedure definition. By setting a default parameter, the developer can choose to exclude those parameters as part of the EXEC statement when calling a stored procedure simplifying code.
Snowflake requires all parameters of a procedure to be passed in the CALL statement and does not allow the setting of default values for input parameters.
Return Values
SQL Server handles return values somewhat by default and does not require them as part of the procedure definition. In the sample procedure, the code simply inserted data into a table and upon success returned the number of rows affected in the insert statement. A specific return value definition was not needed.
Snowflake requires procedures to have a defined return value. For the sample procedure, I chose to return Boolean but could have chosen something different. I also had to specifically include a return statement as part of the definition.
Conclusion
Stored procedures in any DBMS can be powerful tools for executing code blocks in a repeatable manner. Each system has its own set of limitations and features. SQL Server procedures are primarily written in T-SQL which is a really powerful version of SQL. Snowflake has a few more options supporting stored procs written in SQL with Snowflake Scripting, but also supports Python, Java, JavaScript, and Scala.
In this article, I covered a relatively simple use case to build a stored procedure in Snowflake and uncovered a couple of small speed bumps when converting SQL Server procs to Snowflake. In a future post, I cover how to smooth that bump, but also how doing so may create additional ones.
Eric Heilman 
One thought on “Snowflake: Getting Started Stored Procedures”