Snowflake for SQL Server Users - Part 13 - Programmable objects

Similar to SQL Server, Snowflake has ways of creating programmable objects. But the way they work, and the way they are created is quite different.

Functions

Functions are the most similar. You can create them in two ways:

• Javascript
• T-SQL

I like the idea that you can choose which language to write code in, and that they both end up extending the Snowflake SQL language pretty much the same. Some code is better written in T-SQL and other code is better written in a higher-level language.

Functions are called as expected, in place of an expression.

Both scalar functions and table-valued functions are supported. Scalar functions must return a value.

Stored Procedures

There is the ability to write stored procedures, but curiously, you can only do that in Javascript.

I have to say I’ve never worked with a SQL database engine before that supports stored procedures but won’t let you write stored procedures in SQL. I think this is quite a shortcoming in the product.

Stored procedures are called using the CALL statement (not EXEC as in SQL Server). Another curious aspect is that even though the stored procedures support a return value, the syntax for calling stored procedures via CALL doesn’t support retrieving a return value. I have to say, that’s quite bizarre.

You can pass values back from stored procedures by using temporary tables. Or if the returned data is small enough, you might be able to stuff it into a variant data type object and return that.

Stored procedures can be nested.

Triggers

There is currently no concept of a trigger in Snowflake at this time. That means neither DML (INSERT/UPDATE/DELETE) triggers and DDL (CREATE/ALTER/DROP/LOGON) triggers.

As triggers are often a necessary evil in some applications, this again is a significant shortcoming of the product at this time.

 

For an index to all posts in this series, see the first post here.

2019-11-01