SCALAR
Translation reference to convert SQL Server UDF (User Defined Functions) with scalar return type to Snowflake.
Description
Some parts in the output code are omitted for clarity reasons.
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. (SQL Server Language Reference CREATE FUNCTION subsection).
These functions are usually used inside the SELECT
statement, or single variable setup (most likely inside a stored procedure).
SQL Server Syntax
Snowflake Syntax
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.
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. (Scalar SQL UDFs Reference).
Sample Source Patterns
Set and Declare Statements
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.
SQL Server
Snowflake
If/Else Statement Transformation
If/Else statement can be handled in different ways, they can be either transformed to javascript or to SQL using the CASE EXPRESSION 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.