A 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 value. The return value can either be a scalar (single) value or a table. (Transact-SQL Language Reference CREATE FUNCTION)
Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Multi-Statement Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
[ = default ] [READONLY] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
When working with UDF types, it is possible to subcategorize them into simple and complex, according to the inner logic.
Simple User-defined Functions: these functions match the Transact-SQL syntax with Snowflake syntax. This type doesn't add any logic and goes straight to the result. These usually match Snowflake's SQL UDFs.
Complex User-defined Functions: these UDFs extensively use particular language logic operators and usually represent a mismatch or violation of Snowflake's SQL UDFs definition.
Return Types
Scalar functions
Scalar functions return primitive data types such as but not limited to:
DATE, DATETIME, TIME, TIMESTAMP, TIMESTAMP_LTZ,TIMESTAMP_NTZ, TIMESTAMP_TZ
Semi-structured
VARIANT, OBJECT, ARRAY
Geospatial
GEOGRAPHY
Table-valued functions
Table-valued functions return a table-like result. It consists of a set of columns specified in the function's declaration.
Differences
One of the main differences between these two is the amount of supporting languages offered. Snowflake supports up to three languages for the user to declare the UDFs, meanwhile, T-SQL only offers one language in that regard.
Limitations
T-SQL UDFs have some limitations not present in other database engines (such as Oracle and Teradata). These limitations help the translations by narrowing the failure scope. This means, there are specific scenarios we can expect to avoid.
Here are some of the limitations TSQL has on UDFs
UDFs 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 can not return multiple result sets. Use a stored procedure if you need to return multiple result sets.
Sample Source Patterns
Simple User-defined Functions
Simple Scalar functions
Scalar functions can take several optional parameters, and execute a statement to return a single value as the final result. These functions are usually used inside SELECT statements, single variable setup (most likely inside a stored procedure).
Transact-SQL
IN -> SqlServer_01.sql
-- FUNCTION DECLARATION
CREATE FUNCTION sales.udfNetSale(
@quantity INT,
@list_price DEC ( 10, 2 ),
@discount DEC ( 4, 2 )
)
RETURNS DEC ( 10, 2 )
AS
BEGIN
RETURN @quantity * @list_price * ( 1 - @discount );
END;
Snowflake
OUT -> SqlServer_01.sql
-- FUNCTION DECLARATION
CREATE OR REPLACE FUNCTION sales.udfNetSale (QUANTITY INT, LIST_PRICE TEXT, DISCOUNT TEXT)
RETURNS DEC ( 10, 2 )
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
SELECT
QUANTITY * LIST_PRICE * ( 1 - DISCOUNT)
$$;
Function use
-- FUNCTION USE
SELECT sales.udfNetSale ( 10, 100, 0.1 )
;
Simple Table-Valued functions
Table-valued functions take several parameters, execute a query, and return a single value as a final result. These are usually used inside DML statements (SELECT, INSERT, UPDATE, DELETE) to return table-like results.
Transact-SQL
IN -> SqlServer_02.sql
CREATE table production.products (product_name varchar, model_year int, list_price int(10,2));
CREATE FUNCTION udfProductInYear (
@model_year INT
)
RETURNS TABLE
AS
RETURN
SELECT
product_name,
model_year,
list_price
FROM
production.products
WHERE
model_year = @model_year
;
Snowflake
OUT -> SqlServer_02.sql
CREATE OR REPLACE TABLE production.products (
product_name VARCHAR,
model_year INT,
list_price INT(10,2))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
CREATE OR REPLACE FUNCTION udfProductInYear (MODEL_YEAR INT
)
RETURNS TABLE(
product_name VARCHAR,
model_year INTEGER,
list_price INTEGER(10)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
SELECT
product_name,
model_year,
list_price
FROM
production.products
WHERE
model_year = :MODEL_YEAR
$$;
Function Use
-- USE THE FUNCTION, NOTE THE TABLE() TO CONVERT THE RESULT INTO A TABLE
SELECT
product_name,
model_year,
list_price
FROM
TABLE ( udfProductInYear ( 2021 ) )
;
Complex User-defined Functions
Complex UDFs incorporateadditional logical code (Transact-SQL exclusive) to handle different outcomes like nested conditionals and loops. The use of variables is another example of complex UDFs. This level of complexity isn't supported by Snowflake SQL UDFs, instead, it relies on stored procedures and helpers to offer the same functionality. Depending on the target language that was configured for the migration, the functions will be migrated to procedures written in JavaScript or Snowflake Scripting.
Transact-SQL
IN -> SqlServer_03.sql
CREATE OR ALTER FUNCTION get_sign(@num FLOAT)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @result AS VARCHAR(255) = 'negative';
IF @num >= 0
BEGIN
IF @num > 0 SELECT @result = 'positive' ELSE SELECT @result = 'zero';
END
RETURN @result;
END;
Snowflake
OUT -> SqlServer_03.sql
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE get_sign (NUM FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
RESULT VARCHAR(255) := 'negative';
BEGIN
IF (:NUM >= 0) THEN
BEGIN
IF (:NUM > 0) THEN SELECT
'positive'
INTO
:RESULT;
ELSE
SELECT
'zero'
INTO
:RESULT;
END IF;
END;
END IF;
RETURN :RESULT;
END;
$$;
OUT JS -> SqlServer_03.sql
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE get_sign (NUM FLOAT)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code Goes Here
let RESULT = `negative`;
if (NUM >= 0) {
{
if (NUM > 0) {
SELECT(` 'positive'`,[],(value) => RESULT = value);
} else {
SELECT(` 'zero'`,[],(value) => RESULT = value);
}
}
}
return RESULT;
$$;
User-defined Function Calls
Only scalar UDFs can be inside a SELECT statement.
Transact-SQL
-- FUNCTION DECLARATION
CREATE FUNCTION sales.udfNetSale(
@quantity INT,
@list_price DEC ( 10, 2 ),
@discount DEC ( 4, 2 )
)
RETURNS DEC ( 10, 2 )
AS
...
-- FUNCTION USE
SELECT sales.udfNetSale ( 10, 100, 0.1 )
;
Snowflake
-- FUNCTION DECLARATION
CREATE OR REPLACE FUNCTION sales.udfNetSale (
quantity NUMBER,
list_price NUMBER ( 10, 2 ),
discount NUMBER ( 4, 2 )
)
RETURNS NUMBER
AS
$$
...
-- FUNCTION USE
SELECT sales.udfNetSale ( 10, 100, 0.1 )
;
