Functions

Description

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)

Transact-SQL Syntax

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:

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

Simple Scalar Function

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

Simple Scalar Function

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

Simple Table-valued function

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

Simple Table-valued function