SnowflakeDocumentation Home

INLINE TABLE-VALUED

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

Applies to

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

Transact 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

Transact-SQL

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.

Transact-SQL

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.

Transact-SQL

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