INLINE TABLE-VALUED

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

Description

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

CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
);

GO

SELECT * from GetDepartmentInfo()

Snowflake SQL

CREATE OR REPLACE FUNCTION GETDEPARTMENTINFO()
RETURNS TABLE(DEPARTMENTID NUMBER(38,0), NAME VARCHAR(50), GROUPNAME VARCHAR(50))
AS
$$
    SELECT DEPARTMENTID, NAME, GROUPNAME
    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

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

CREATE OR REPLACE FUNCTION GETPERSONBASICINFO()
RETURNS TABLE(PERSONTYPE NCHAR(2), FIRSTNAME VARCHAR(50), JOBTITLE VARCHAR(50), GENDER NCHAR(1), HIREYEAR NUMBER)
AS 
$$
	
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
$$
   

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

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

CREATE OR REPLACE FUNCTION GETMARITALSTATUSBYGENDER
(
  P_GENDER NCHAR(1)
)

RETURNS TABLE (MARITALSTATUS NCHAR(1), GENDER NCHAR(1), NAME STRING)

AS

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

SELECT * FROM TABLE(GETMARITALSTATUSBYGENDER('F'));

Known issues

No issues were found

Related EWIs

  1. MSCEWI4029: Information for the column was not found. CAST as STRING is used to match the datatypes in the return columns.

  2. MSCEWI4031: Information for the expression was not found. CAST to STRING used.

  3. MSCEWI4034: RETURNS clause incomplete due to missing symbols

PreviousSCALARNextMULTI-STATEMENT TABLE-VALUED

Last updated