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
StatementWITH
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
MSCEWI4029: Information for the column was not found. CAST as STRING is used to match the datatypes in the return columns.
MSCEWI4031: Information for the expression was not found. CAST to STRING used.
MSCEWI4034:
RETURNS
clause incomplete due to missing symbols
Last updated
Was this helpful?