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)

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

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

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

--** SSC-FDM-0029 - 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 )
;

Known Issues

No issues were found.

SSC-FDM-0029: User defined function was transformed to a Snowflake procedure.

PreviousProcedures NextDMLs

Last updated 6 months ago

Description

Return Types

Scalar functions

Table-valued functions

Differences

Limitations

Sample Source Patterns

Simple User-defined Functions

Simple Scalar functions

Function use

Simple Table-Valued functions

Function Use

Complex User-defined Functions

User-defined Function Calls

Transact-SQL

Snowflake

Known Issues

Related EWIs