Last updated
Last updated
A scalar user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a scalar value. ().
Snowflake allows 3 different languages in their user defined functions:
SQL
JavaScript
Java
For now, SnowConvert will support only SQL
and JavaScript
as target languages.
The most common statements in function bodies are the DECLARE
and SET
statements. For DECLARE
statements without default value, the transformation will be ignored. SET
statements and DECLARE
statements with a default value, will be transformed to a COMMON TABLE EXPRESSION.
Each common table expression will contain a column that represents the local variable value.
For nested statements, the structured programming is being transformed to a single query. The statements in the control-of-flow are going to be nested in table structures in order to preserve the execution order.
Variable definition and assignment within conditional statements tends to be somewhat problematic, because references to the variable further down the code would have to know where the variable was last modified. Not only that, but if the reference is within another conditional statement, then there would have to be some kind of redirect that references the previous known assignment to the variable.
In the following scenario, the first IF
statement can be transformed without problems, because the contents are straightforward enough. The second and third IF
statements are commented out because they're not supported at the moment, since there are statements other than variable assignments through SELECT
.
For this specific pattern there are no obvious queries, but there are multiple calls to multiple functions working on the same variable and returning it at the end. Since Snowflake only supports queries inside its functions, the solution for this block is going to be adding it to a Select and nesting the calls inside, making sure the return value is the same as the one on the source.
For this pattern, a variable is modified (increased in this case) using multiple IF conditions. In the beginning, a set of variables is initialized and used to determine whether the result variable should be increased or not. Finally, the result variable is returned.
For this pattern, the IF
block containing the return clause that breaks the code flow is added at the end of the body, like the final statement to be executed in a CASE
expression.
For this particular scenario, there is no logic between the conditional RETURN
statement and the final RETURN
statement, so all body will be mapped to a single CASE EXPRESSION
.
Common table expressions will be kept as in the original code, and they are going to be concatenated with the generated ones. SnowConvert is able to identify first all the original COMMON TABLE EXPRESSION
names in order to avoid generating duplicated names.
If there are multiple statements and the function does not access the database in any way, it can be transformed into a JavaScript function keeping the functional equivalence
User-defined functions cannot be used to perform actions that modify the database state
User-defined functions cannot contain an OUTPUT INTO
clause that has a table as its target
User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR
. Use a Stored Procedure if you need to use cursors.
User-defined functions cannot perform control-of-flow statements such as WHILE if there is at least one call to the database
User-defined functions with references to other user-defined functions that were transformed to Stored Procedures, will be transformed to Stored Procedures too.
For all the unsupported cases, please check the related EWIs and the patterns below to obtain recommendations and possible workarounds.
The next scenario involves the use of the "while statement" along side other queries. The problem with this example is that there's no way of transforming the while statement to a CTE inside the WITH
clause of the main select, this forces us to transform this statement to JavaScript procedure to maintain the same logic.
Snowflake
In the following example, the variable @names
is used to concatenate multiple values from a column into one single string. The variable is updated on each iteration as shown, which is not supported by SnowFlake UDFs. For this scenario, the function should be transformed into a procedure.
SQL Server
Snowflake query
For the described scenarios above, consider the following limitations:
All the calls to user-defined functions in DML queries such as SELECT
, INSERT
, DELETE
, UPDATE
or MERGE
will fail because calls to Stored Procedures within these queries are not allowed.
Calls to user-defined functions inside procedures, should be preceeded by the CALL
keyword.
SQL user defined functions only supports one query as their body. They can read from the database, but is not allowed to write or modify it. ().
If/Else statement can be handled in different ways, they can be either transformed to javascript or to SQL using the inside the select allowing conditionals inside the queries, while the javascript transformation is pretty straightforward, the Case statement might not be so obvious at first glance.
This is all aggravated by nesting and complex querying that can be found on input code. That's why a specific is added when these patterns are found.
In this simple pattern, there is a variable declaration, then, that variable is set using a SELECT
statement and finally returned. This is going to be migrated to a in order to keep the original behavior.
User-defined functions that use are not supported in SQL and should be transformed to stored procedures in order to keep the functional equivalence.
User-defined functions that have SELECT
statements assigning a variable to itself is not supported in Snowflake. See also
Use- defined functions used in will fail during the execution.
: UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.
: Pending Functional Equivalence Review.
: User defined function was transformed to a Snowflake procedure.
Translation reference to convert Transact-SQL UDF (User Defined Functions) with scalar return type to Snowflake.