INLINE TABLE-VALUED

Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.

Description

Some parts in the output code are omitted for clarity reasons.

Inline Table-Valued functions are table expression that can accept parameters, perform a SELECT statement and return a TABLE (SQL Server Language Reference Creating an inline table-valued function).

SQL Server Syntax

-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Snowflake SQL Syntax

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'sql

Sample Source Patterns

The following section describes all the possible source code patterns that can appear in this kind of CREATE FUNCTION syntax.

For Inline Table-Valued functions, there can only exist one statement per body that could be:

  • SELECT Statement

  • WITH Common Table Expression

Select and return values directly from one table

This is the simplest scenario, performing a simple select from a table and returning those values

SQL Server

IN -> SqlServer_01.sql
CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
);

GO

SELECT * from GetDepartmentInfo()

Snowflake SQL

OUT -> SqlServer_01.sql
CREATE OR REPLACE FUNCTION GetDepartmentInfo ()
RETURNS TABLE(
  DepartmentID STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN DepartmentID WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  Name STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Name WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  GroupName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN GroupName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
    SELECT
    CAST(DepartmentID AS STRING),
    CAST(Name AS STRING),
    CAST(GroupName AS STRING)
    FROM
    HumanResources.Department
$$;

SELECT
    *
from
    TABLE(GetDepartmentInfo());

Select and return values from multiple tables renaming columns and using built in functions

This is an example of a query using built-in functions in a select statement getting data from different tables, renaming columns and returning a table.

SQL Server

IN -> SqlServer_02.sql
CREATE FUNCTION GetPersonBasicInfo()
RETURNS TABLE
AS 
RETURN
(	
 SELECT TOP (20)
      P.PersonType,
      P.FirstName,
      E.JobTitle,
	  E.Gender,
      YEAR(E.HireDate) as HIREYEAR
  FROM 
      Person.Person P
  INNER JOIN 
      HumanResources.Employee E
  ON
      P.BusinessEntityID = E.BusinessEntityID 
);

GO

SELECT * FROM GetPersonBasicInfo();

Snowflake SQL

OUT -> SqlServer_02.sql
CREATE OR REPLACE FUNCTION GetPersonBasicInfo ()
RETURNS TABLE(
	PersonType STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN PersonType WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	FirstName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN FirstName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	JobTitle STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN JobTitle WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	HIREYEAR INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
	 SELECT
	 TOP 20
		CAST(P.PersonType AS STRING),
		CAST(P.FirstName AS STRING),
		CAST(E.JobTitle AS STRING),
		CAST(E.Gender AS STRING),
		YEAR(E.HireDate :: TIMESTAMP) as HIREYEAR
	  FROM
		Person.Person P
	  INNER JOIN
			HumanResources.Employee E
	  ON P.BusinessEntityID = E.BusinessEntityID
$$;

SELECT
	 *
FROM
	 TABLE(GetPersonBasicInfo());

Select columns using WITH statement

The body of an inline table-valued function can also be specified using a WITH statement as shown below.

SQL Server

IN -> SqlServer_03.sql
CREATE FUNCTION GetMaritalStatusByGender
(
	@P_Gender nchar(1)
)

RETURNS TABLE 
AS
RETURN
(
  WITH CTE AS 
	(
	 SELECT BusinessEntityID, MaritalStatus, Gender 
	 FROM HumanResources.Employee 
	 where Gender = @P_Gender
	)	
  SELECT 
	MaritalStatus, Gender, CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM 
	CTE INNER JOIN Person.Person P
  ON
	CTE.BusinessEntityID = P.BusinessEntityID
);

GO

select * from GetMaritalStatusByGender('F');

Snowflake SQL

OUT -> SqlServer_03.sql
CREATE OR REPLACE FUNCTION GetMaritalStatusByGender
(P_GENDER STRING
)
RETURNS TABLE(
	MaritalStatus STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN MaritalStatus WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Name VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
	--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
  WITH CTE AS
	(
	 SELECT
	 	BusinessEntityID,
	 	MaritalStatus,
	 	Gender
	 FROM
	 	HumanResources.Employee
	 where
	 	Gender = :P_GENDER
	)
  SELECT
	 CAST(MaritalStatus AS STRING),
	 CAST(Gender AS STRING),
	 CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM
	 CTE
	 INNER JOIN
	 	Person.Person P
  ON CTE.BusinessEntityID = P.BusinessEntityID
$$;

select
  *
from GetMaritalStatusByGender('F') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TableValuedFunctionCall' NODE ***/!!!;

Known issues

No issues were found

Related EWIs

  1. SSC-FDM-TS0012: Information for the expression was not found. CAST to STRING used

  2. SSC-PRF-TS0001: Performance warning - recursion for CTE not checked. Might require a recursive keyword.

  3. SSC-EWI-0073: Pending Functional Equivalence Review

PreviousSCALARNextMULTI-STATEMENT TABLE-VALUED

Last updated