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:

Category	Data Types
Numeric	NUMBER, DECIMAL, NUMERIC, INT, DOUBLE, REAL
String & Binary	VARCHAR, CHAR, STRING, TEXT, BINARY, VARBINARY Logical
Logical	BOOLEAN
Date & Time	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

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

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 incorporate additional 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

Complex User-defined Scalar Function

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

Snowflake Scripting

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;
$$;

JavaScript

User-defined Function Calls

Only scalar UDFs can be inside a SELECT statement.

Transact-SQL

User-defined Function call inside a query

-- 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

User-defined Function call inside a query

-- 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 )
;

Known Issues

No issues were found.

Related EWIs

1. SSC-EWI-0068: User defined function was transformed to a Snowflake procedure.