Snowflake: Overloading Stored Procedures

In a previous post, I discussed some of the struggles I had with converting a stored procedure from SQL Server to Snowflake – namely, the ability to set input parameters to default values. One way to get around this issue is to “overload” the procedure in Snowflake. Snowflake supports polymorphism whereas SQL Server does not.

NOTE: Since this article was published, Snowflake added a feature to allow for default parameter values. See this post for an update.

What is Polymorphism?

Polymorphism, sometimes referred to as “overloading”, is defining a routine of the same name in the same namespace with a different number of inputs, or inputs of different types. We see this frequently in C++, C#, or Python where a single function like new() can take no parameters, or a dozen, or one. Each definition of new() can be handled differently and/or “nest” into another definition of new().

Using Polymorphism in Snowflake

Snowflake supports polymorphism with functions and stored procedures by permitting objects of the same name in the same schema but with different signatures. Using the example from the previous post for an event log handler, I’ll show how to deploy a stored procedure of the same name, but with a different signature.

Prior Stored Procedure Signature

In the previous post, I created a stored procedure USP_EVT_LOG that accepts two VARCHAR input parameters. During the creation, I attempted to set the EVT_TYPE parameter to a default value; however, ran into difficulty doing so. My SQL Server version used the default of “INFO” for EVT_TYPE to simplify procedure calls for non-error events. I got around the default issue by passing NULL for the EVT_TYPE parameter and handled the NULL via code.

/*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');

Creating a Second Stored Procedure

Using the object-oriented programming concept of polymorphism, I can create another stored procedure of the same name, but with a different signature to handle the missing default value. There are a few different options for doing so, one would be to replicate the “guts” of the original procedures and set the default in the insert statement. The disadvantage with this method is the code is now in two different places. If the table structure changes, I would need to alter two procedures to make changes.

Another option, and the one I’ll use, is to use the second procedure as a wrapper to the first procedure and call the first procedure from the second procedure passing my desired default value for EVT_TYPE.

CREATE OR REPLACE PROCEDURE USP_EVT_LOG(EVT_DESC VARCHAR(1000))
RETURNS BOOLEAN
AS
DECLARE
    RETURN_VALUE BOOLEAN DEFAULT FALSE;
BEGIN
    RETURN_VALUE := (CALL USP_EVT_LOG('INFO',:EVT_DESC));
    RETURN RETURN_VALUE;
END;

Notice that I had to define the proper return value variable and assign the output of the first stored procedure call to it, then return the value to get the same behavior as the original procedure. Failing to do so still creates a working procedure, but will return a NULL value. I now have two procedures of the same name but with different numbers of parameters.

Polymorphism Caveats

While creating two procedures with the same name and different signatures solved the default parameter value problem, it is not without its caveats.

Specify Arguments on Commands

Since each procedure is its own object, the argument types need to be included with any command that references the procedure by name, such as changing ownership.

GRANT OWNERSHIP ON PROCEDURE USP_EVT_LOG TO ROLE SYSADMIN;

The command above produces this error:

To properly change ownership, the arguments need to be included with the procedure name:

GRANT OWNERSHIP ON PROCEDURE USP_EVT_LOG(VARCHAR,VARCHAR) TO ROLE SYSADMIN;
GRANT OWNERSHIP ON PROCEDURE USP_EVT_LOG(VARCHAR) TO ROLE SYSADMIN;

The same applies to other types of commands including DROP or SET COMMENT.

COMMENT ON PROCEDURE USP_EVT_LOG(VARCHAR) IS 'DEFAULTS EVT_TYPE TO "INFO"';

Unique Argument Signatures

In my example, the second USP_EVT_LOG has a single VARCHAR input for EVT_DESC, but what if I wanted a third proc that had a single VARCHAR input for EVT_TYPE? Altering the original code to swap the input parameters doesn’t produce an error; however, it does replace the procedure I created earlier:

CREATE OR REPLACE PROCEDURE USP_EVT_LOG(EVT_TYPE VARCHAR(1000))
RETURNS BOOLEAN
AS
DECLARE
    RETURN_VALUE BOOLEAN DEFAULT FALSE;
BEGIN
    RETURN_VALUE := (CALL USP_EVT_LOG(:EVT_TYPE,'DEFAULT DESCRIPTION'));
    RETURN RETURN_VALUE;
END;

Notice I still have only two procedures after executing the code above:

If I remove “OR REPLACE” from the code block above, I get an error that USP_EVT_LOG already exists, meaning procedure signatures must be unique by name, argument number, and type.

Conclusion

Snowflake supports polymorphism by allowing objects of the same name in the same namespace which can be used to create different results or use a different set of inputs; however, doing so is not without its own set of caveats to keep in mind when using this feature.