SnowConvert AI - SQL Server - CREATE FUNCTION¶
Translation reference for the Transact-SQL User Defined Functions
Applies to
SQL Server
Azure Synapse Analytics
Description¶
SQL Server only supports two types of User Defined Functions:
Using these UDFs types, is possible to subcategorized them into simple and complex, according to the inner logic.
Simple UDFs, matches the SQL Server syntax with Snowflake syntax. This type doesn’t add any logic and goes straightforward to the result. These are usually match to Snowflake’s SQL UDFs.
Complex UDFs, makes extensive use of a particular statements (INSERT, DELETE, UPDATE, SET, DECLARE, etc) or control-of-flow blocks (IF…ELSE, WHILE, etc) and usually represents a mismatch or violation to Snowflake’s SQL UDFs definition.
Limitations¶
Transact UDFs have some limitations not present in other database engines (such as Oracle and Teradata). These limitations helps the translations by narrowing the failure scope. This means, there are specific scenarios we can expect to avoid.
Here are some of the limitations SQL Server 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 cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.
For the full list, please check this link Create User-defined Functions (Database engine)
INLINE TABLE-VALUED¶
Translation reference to convert Transact-SQL UDF (User Defined Functions) with TABLE return type to Snowflake.
Applies to
SQL Server
Azure Synapse Analytics
Description¶
Note
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
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
Transact-SQL¶
Inline Table-Valued¶
CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
);
GO
SELECT * from GetDepartmentInfo()
Result¶
DepartmentID |
Name |
GroupName |
---|---|---|
1 |
Engineering |
Research and Development |
2 |
Tool Design |
Research and Development |
3 |
Sales |
Sales and Marketing |
4 |
Marketing |
Sales and Marketing |
5 |
Purchasing |
Inventory Management |
6 |
Research and Development |
Research and Development |
7 |
Production |
Manufacturing |
8 |
Production Control |
Manufacturing |
9 |
Human Resources |
Executive General and Administration |
10 |
Finance |
Executive General and Administration |
11 |
Information Services |
Executive General and Administration |
12 |
Document Control |
Quality Assurance |
13 |
Quality Assurance |
Quality Assurance |
14 |
Facilities and Maintenance |
Executive General and Administration |
15 |
Shipping and Receiving |
Inventory Management |
16 |
Executive |
Executive General and Administration |
Snowflake SQL¶
Inline Table-Valued¶
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());
Result¶
DepartmentID |
Name |
GroupName |
---|---|---|
1 |
Engineering |
Research and Development |
2 |
Tool Design |
Research and Development |
3 |
Sales |
Sales and Marketing |
4 |
Marketing |
Sales and Marketing |
5 |
Purchasing |
Inventory Management |
6 |
Research and Development |
Research and Development |
7 |
Production |
Manufacturing |
8 |
Production Control |
Manufacturing |
9 |
Human Resources |
Executive General and Administration |
10 |
Finance |
Executive General and Administration |
11 |
Information Services |
Executive General and Administration |
12 |
Document Control |
Quality Assurance |
13 |
Quality Assurance |
Quality Assurance |
14 |
Facilities and Maintenance |
Executive General and Administration |
15 |
Shipping and Receiving |
Inventory Management |
16 |
Executive |
Executive General and Administration |
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¶
Inline Table-Valued¶
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();
Result¶
PersonType |
FirstName |
JobTitle |
Gender |
HIREYEAR |
---|---|---|---|---|
EM |
Ken |
Chief Executive Officer |
M |
2009 |
EM |
Terri |
Vice President of Engineering |
F |
2008 |
EM |
Roberto |
Engineering Manager |
M |
2007 |
EM |
Rob |
Senior Tool Designer |
M |
2007 |
EM |
Gail |
Design Engineer |
F |
2008 |
EM |
Jossef |
Design Engineer |
M |
2008 |
EM |
Dylan |
Research and Development Manager |
M |
2009 |
EM |
Diane |
Research and Development Engineer |
F |
2008 |
EM |
Gigi |
Research and Development Engineer |
F |
2009 |
EM |
Michael |
Research and Development Manager |
M |
2009 |
EM |
Ovidiu |
Senior Tool Designer |
M |
2010 |
EM |
Thierry |
Tool Designer |
M |
2007 |
EM |
Janice |
Tool Designer |
F |
2010 |
EM |
Michael |
Senior Design Engineer |
M |
2010 |
EM |
Sharon |
Design Engineer |
F |
2011 |
EM |
David |
Marketing Manager |
M |
2007 |
EM |
Kevin |
Marketing Assistant |
M |
2007 |
EM |
John |
Marketing Specialist |
M |
2011 |
EM |
Mary |
Marketing Assistant |
F |
2011 |
EM |
Wanida |
Marketing Assistant |
F |
2011 |
Snowflake SQL¶
Inline Table-Valued¶
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());
Result¶
PersonType |
FirstName |
JobTitle |
Gender |
HIREYEAR |
---|---|---|---|---|
EM |
Ken |
Chief Executive Officer |
M |
2009 |
EM |
Terri |
Vice President of Engineering |
F |
2008 |
EM |
Roberto |
Engineering Manager |
M |
2007 |
EM |
Rob |
Senior Tool Designer |
M |
2007 |
EM |
Gail |
Design Engineer |
F |
2008 |
EM |
Jossef |
Design Engineer |
M |
2008 |
EM |
Dylan |
Research and Development Manager |
M |
2009 |
EM |
Diane |
Research and Development Engineer |
F |
2008 |
EM |
Gigi |
Research and Development Engineer |
F |
2009 |
EM |
Michael |
Research and Development Manager |
M |
2009 |
EM |
Ovidiu |
Senior Tool Designer |
M |
2010 |
EM |
Thierry |
Tool Designer |
M |
2007 |
EM |
Janice |
Tool Designer |
F |
2010 |
EM |
Michael |
Senior Design Engineer |
M |
2010 |
EM |
Sharon |
Design Engineer |
F |
2011 |
EM |
David |
Marketing Manager |
M |
2007 |
EM |
Kevin |
Marketing Assistant |
M |
2007 |
EM |
John |
Marketing Specialist |
M |
2011 |
EM |
Mary |
Marketing Assistant |
F |
2011 |
EM |
Wanida |
Marketing Assistant |
F |
2011 |
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¶
Inline Table-Valued¶
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');
Result¶
MaritalStatus |
Gender |
Name |
---|---|---|
S |
F |
Terri Duffy |
M |
F |
Gail Erickson |
S |
F |
Diane Margheim |
M |
F |
Gigi Matthew |
M |
F |
Janice Galvin |
M |
F |
Sharon Salavaria |
S |
F |
Mary Dempsey |
M |
F |
Wanida Benshoof |
M |
F |
Mary Gibson |
M |
F |
Jill Williams |
S |
F |
Jo Brown |
M |
F |
Britta Simon |
M |
F |
Margie Shoop |
M |
F |
Rebecca Laszlo |
M |
F |
Suchitra Mohan |
M |
F |
Kim Abercrombie |
S |
F |
JoLynn Dobney |
M |
F |
Nancy Anderson |
M |
F |
Ruth Ellerbrock |
M |
F |
Doris Hartwig |
M |
F |
Diane Glimp |
M |
F |
Bonnie Kearney |
M |
F |
Denise Smith |
S |
F |
Diane Tibbott |
M |
F |
Carole Poland |
M |
F |
Carol Philips |
M |
F |
Merav Netz |
S |
F |
Betsy Stadick |
S |
F |
Danielle Tiedt |
S |
F |
Kimberly Zimmerman |
M |
F |
Elizabeth Keyser |
M |
F |
Mary Baker |
M |
F |
Alice Ciccu |
M |
F |
Linda Moschell |
S |
F |
Angela Barbariol |
S |
F |
Kitti Lertpiriyasuwat |
S |
F |
Susan Eaton |
S |
F |
Kim Ralls |
M |
F |
Nicole Holliday |
S |
F |
Anibal Sousa |
M |
F |
Samantha Smith |
S |
F |
Olinda Turner |
S |
F |
Cynthia Randall |
M |
F |
Sandra Reátegui Alayo |
S |
F |
Linda Randall |
S |
F |
Shelley Dyck |
S |
F |
Laura Steele |
S |
F |
Susan Metters |
S |
F |
Katie McAskill-White |
M |
F |
Barbara Decker |
M |
F |
Yvonne McKay |
S |
F |
Janeth Esteves |
M |
F |
Brenda Diaz |
M |
F |
Lorraine Nay |
M |
F |
Paula Nartker |
S |
F |
Lori Kane |
M |
F |
Kathie Flood |
S |
F |
Belinda Newman |
M |
F |
Karen Berge |
M |
F |
Lori Penor |
M |
F |
Jo Berry |
M |
F |
Laura Norman |
M |
F |
Paula Barreto de Mattos |
M |
F |
Mindy Martin |
M |
F |
Deborah Poe |
S |
F |
Candy Spoon |
M |
F |
Barbara Moreland |
M |
F |
Janet Sheperdigian |
S |
F |
Wendy Kahn |
S |
F |
Sheela Word |
M |
F |
Linda Meisner |
S |
F |
Erin Hagens |
M |
F |
Annette Hill |
S |
F |
Jean Trenary |
S |
F |
Stephanie Conroy |
S |
F |
Karen Berg |
M |
F |
Janaina Bueno |
M |
F |
Linda Mitchell |
S |
F |
Jillian Carson |
S |
F |
Pamela Ansman-Wolfe |
S |
F |
Lynn Tsoflias |
M |
F |
Amy Alberts |
S |
F |
Rachel Valdez |
M |
F |
Jae Pak |
Snowflake SQL¶
Inline Table-Valued¶
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "HumanResources.Employee", "Person.Person" **
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": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "07/11/2025", "domain": "no-domain-provided" }}'
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
TABLE(GetMaritalStatusByGender('F'));
Result¶
MaritalStatus |
Gender |
Name |
---|---|---|
S |
F |
Terri Duffy |
M |
F |
Gail Erickson |
S |
F |
Diane Margheim |
M |
F |
Gigi Matthew |
M |
F |
Janice Galvin |
M |
F |
Sharon Salavaria |
S |
F |
Mary Dempsey |
M |
F |
Wanida Benshoof |
M |
F |
Mary Gibson |
M |
F |
Jill Williams |
S |
F |
Jo Brown |
M |
F |
Britta Simon |
M |
F |
Margie Shoop |
M |
F |
Rebecca Laszlo |
M |
F |
Suchitra Mohan |
M |
F |
Kim Abercrombie |
S |
F |
JoLynn Dobney |
M |
F |
Nancy Anderson |
M |
F |
Ruth Ellerbrock |
M |
F |
Doris Hartwig |
M |
F |
Diane Glimp |
M |
F |
Bonnie Kearney |
M |
F |
Denise Smith |
S |
F |
Diane Tibbott |
M |
F |
Carole Poland |
M |
F |
Carol Philips |
M |
F |
Merav Netz |
S |
F |
Betsy Stadick |
S |
F |
Danielle Tiedt |
S |
F |
Kimberly Zimmerman |
M |
F |
Elizabeth Keyser |
M |
F |
Mary Baker |
M |
F |
Alice Ciccu |
M |
F |
Linda Moschell |
S |
F |
Angela Barbariol |
S |
F |
Kitti Lertpiriyasuwat |
S |
F |
Susan Eaton |
S |
F |
Kim Ralls |
M |
F |
Nicole Holliday |
S |
F |
Anibal Sousa |
M |
F |
Samantha Smith |
S |
F |
Olinda Turner |
S |
F |
Cynthia Randall |
M |
F |
Sandra Reátegui Alayo |
S |
F |
Linda Randall |
S |
F |
Shelley Dyck |
S |
F |
Laura Steele |
S |
F |
Susan Metters |
S |
F |
Katie McAskill-White |
M |
F |
Barbara Decker |
M |
F |
Yvonne McKay |
S |
F |
Janeth Esteves |
M |
F |
Brenda Diaz |
M |
F |
Lorraine Nay |
M |
F |
Paula Nartker |
S |
F |
Lori Kane |
M |
F |
Kathie Flood |
S |
F |
Belinda Newman |
M |
F |
Karen Berge |
M |
F |
Lori Penor |
M |
F |
Jo Berry |
M |
F |
Laura Norman |
M |
F |
Paula Barreto de Mattos |
M |
F |
Mindy Martin |
M |
F |
Deborah Poe |
S |
F |
Candy Spoon |
M |
F |
Barbara Moreland |
M |
F |
Janet Sheperdigian |
S |
F |
Wendy Kahn |
S |
F |
Sheela Word |
M |
F |
Linda Meisner |
S |
F |
Erin Hagens |
M |
F |
Annette Hill |
S |
F |
Jean Trenary |
S |
F |
Stephanie Conroy |
S |
F |
Karen Berg |
M |
F |
Janaina Bueno |
M |
F |
Linda Mitchell |
S |
F |
Jillian Carson |
S |
F |
Pamela Ansman-Wolfe |
S |
F |
Lynn Tsoflias |
M |
F |
Amy Alberts |
S |
F |
Rachel Valdez |
M |
F |
Jae Pak |
Known issues¶
No issues were found
MULTI-STATEMENT TABLE-VALUED¶
Translation reference to convert Transact-SQL UDF (User Defined Functions) with TABLE return type to Snowflake.
Applies to
SQL Server
Azure Synapse Analytics
Note
Some parts in the output code are omitted for clarity reasons.
Note
All the code samples on this page have not been implemented yet in SnowConvert AI. They should be interpreted as a reference for how each scenario should be translated to Snowflake. These translations may change in the future.Some parts in the output code are omitted for clarity reasons.
Description¶
Multi-statement table-valued is similar to Inline-statement table-valued (INLINE TABLE-VALUED). However Multi-statement table-valued may have more than one statement in its function body, the table columns are specified in the return type and it has a BEGIN/END block (SQL Server Language Reference Creating a multi-statement table-valued function
Transact-SQL Syntax¶
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [READONLY] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
Snowflake SQL¶
CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
AS '<sql_expression>'
Sample Source Patterns¶
The following section describes all the possible source code patterns that can appear in this kind ofCREATE FUNCTION syntax.
The function body of Multi-Statement Table-Valued function must be a SELECT statement. For this reason the others statements must be called separately.
Insert values in a table¶
Inserts one or more rows into the table and returns the table with the new values
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
CREATE OR ALTER FUNCTION calc_behavioral_segment()
RETURNS @behavioral_segments TABLE (behavioral_segment VARCHAR(50))
AS
BEGIN
DECLARE @col varchar(15)
SET @col = 'Unknown'
INSERT INTO @behavioral_segments
SELECT @col
RETURN
END
SELECT * FROM calc_behavioral_segment();
Result¶
BEHAVIORAL_SEGMENT |
---|
Unknown |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION calc_behavioral_segment ()
RETURNS BEHAVIORAL_SEGMENTS TABLE (
behavioral_segment VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @col varchar(15)
SET @col = 'Unknown'
INSERT INTO @behavioral_segments
SELECT @col
RETURN
END
SELECT * FROM calc_behavioral_segment();;
Results¶
BEHAVIORAL_SEGMENT |
---|
Unknown |
Insert value according to if/else statement¶
Inserts a row into the table according to the condition and returns the table with the new value
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
CREATE OR ALTER FUNCTION odd_or_even_number(@number INT)
RETURNS @numbers TABLE (number_type VARCHAR(15))
AS
BEGIN
IF ((@number % 2) = 0)
BEGIN
INSERT @numbers SELECT 'Even'
END
ELSE
BEGIN
INSERT @numbers SELECT 'Odd'
END
RETURN
END
SELECT * FROM odd_or_even_number(9);
Result¶
NUMBER_TYPE |
---|
Odd |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION odd_or_even_number (NUMBER INT)
RETURNS NUMBERS TABLE (
number_type VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
IF ((@number % 2) = 0)
BEGIN
INSERT @numbers SELECT 'Even'
END
ELSE
BEGIN
INSERT @numbers SELECT 'Odd'
END
RETURN
END
SELECT * FROM odd_or_even_number(9);;
Result¶
NUMBER_TYPE |
---|
Odd |
Inserts multiple according to if/else statement¶
The example below inserts more than one value into the table and more than one variable is modified according to the condition. Returns the table with the new values
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
CREATE OR ALTER FUNCTION new_employee_hired(@id VARCHAR (50), @position VARCHAR(50), @experience VARCHAR(15))
RETURNS @new_employee TABLE (id_employee VARCHAR (50), working_from_home BIT, team VARCHAR(15), computer VARCHAR(15))
AS
BEGIN
DECLARE @wfh BIT
DECLARE @team VARCHAR(15)
DECLARE @computer VARCHAR(15)
IF @position = 'DEVELOPER'
BEGIN
SET @team = 'TEAM_1'
SET @computer = 'LAPTOP'
END
IF @position = 'IT'
BEGIN
SET @team = 'TEAM_2'
SET @computer = 'DESKTOP'
END
IF @experience = 'JUNIOR'
BEGIN
SET @wfh = '0'
END
IF @experience = 'SENIOR'
BEGIN
SET @wfh = '1'
END
INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
RETURN
END
SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');
Result¶
ID_EMPLOYEE |
WORKING_FROM_HOME |
TEAM |
COMPUTER |
---|---|---|---|
123456789 |
1 |
TEAM_1 |
LAPTOP |
Snowflake¶
MULTI-STATEMENT TABLE-VALUED¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION new_employee_hired (ID STRING, POSITION STRING, EXPERIENCE STRING)
RETURNS NEW_EMPLOYEE TABLE (
id_employee VARCHAR(50),
working_from_home BOOLEAN,
team VARCHAR(15),
computer VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @wfh BIT
DECLARE @team VARCHAR(15)
DECLARE @computer VARCHAR(15)
IF @position = 'DEVELOPER'
BEGIN
SET @team = 'TEAM_1'
SET @computer = 'LAPTOP'
END
IF @position = 'IT'
BEGIN
SET @team = 'TEAM_2'
SET @computer = 'DESKTOP'
END
IF @experience = 'JUNIOR'
BEGIN
SET @wfh = '0'
END
IF @experience = 'SENIOR'
BEGIN
SET @wfh = '1'
END
INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
RETURN
END
SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');;
Result¶
ID_EMPLOYEE |
WORKING_FROM_HOME |
TEAM |
COMPUTER |
---|---|---|---|
123456789 |
1 |
TEAM_1 |
LAPTOP |
Warning
In case there are nested if statements and more than one variables are modified in the statements it is necessary to use a stored procedure.
Update values previously inserted¶
Updates columns values of the table into the function body and returns it with the new values.
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
CREATE OR ALTER FUNCTION get_employees_history()
RETURNS @employee_history TABLE (
department_name NVARCHAR(50),
first_name NVARCHAR(50),
last_name NVARCHAR(50),
start_date DATE,
end_date DATE,
job_title NVARCHAR(50),
months_working INT
)
BEGIN
INSERT INTO @employee_history
SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
LEFT OUTER JOIN employee_department_history EH
ON D.department_ID = EH.department_ID
INNER JOIN Employee E
ON E.business_entity_ID = EH.business_entity_ID
INNER JOIN Person P
ON P.business_entity_ID = E.business_entity_ID
UPDATE @employee_history
SET
months_working =
CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
ELSE DATEDIFF(MONTH, start_date, end_date)
END
RETURN;
END;
SELECT TOP(10) * FROM get_employees_history();
Result¶
DEPARTMENT_NAME |
FIRST_NAME |
LAST_NAME |
START_DATE |
END_DATE |
JOB_TITLE |
MONTHS_WORKING |
---|---|---|---|---|---|---|
Sales |
Syed |
Abbas |
2013-03-14 |
NULL |
Pacific Sales Manager |
106 |
Production |
Kim |
Abercrombie |
2010-01-16 |
NULL |
Production Technician - WC60 |
144 |
Quality Assurance |
Hazem |
Abolrous |
2009-02-28 |
NULL |
Quality Assurance Manager |
155 |
Shipping and Receiving |
Pilar |
Ackerman |
2009-01-02 |
NULL |
Shipping and Receiving Supervisor |
156 |
Production |
Jay |
Adams |
2009-03-05 |
NULL |
Production Technician - WC60 |
154 |
Information Services |
François |
Ajenstat |
2009-01-17 |
NULL |
Database Administrator |
156 |
Sales |
Amy |
Alberts |
2012-04-16 |
NULL |
European Sales Manager |
117 |
Production |
Greg |
Alderson |
2008-12-02 |
NULL |
Production Technician - WC45 |
157 |
Quality Assurance |
Sean |
Alexander |
2008-12-28 |
NULL |
Quality Assurance Technician |
157 |
Facilities and Maintenance |
Gary |
Altman |
2009-12-02 |
NULL |
Facilities Manager |
145 |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_employees_history ()
RETURNS EMPLOYEE_HISTORY TABLE (
department_name VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
start_date DATE,
end_date DATE,
job_title VARCHAR(50),
months_working INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
BEGIN
INSERT INTO @employee_history
SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
LEFT OUTER JOIN employee_department_history EH
ON D.department_ID = EH.department_ID
INNER JOIN Employee E
ON E.business_entity_ID = EH.business_entity_ID
INNER JOIN Person P
ON P.business_entity_ID = E.business_entity_ID
UPDATE @employee_history
SET
months_working =
CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
ELSE DATEDIFF(MONTH, start_date, end_date)
END
RETURN;
END;
SELECT TOP(10) * FROM get_employees_history();;
Result¶
DEPARTMENT_NAME |
FIRST_NAME |
LAST_NAME |
START_DATE |
END_DATE |
JOB_TITLE |
MONTHS_WORKING |
---|---|---|---|---|---|---|
Sales |
Syed |
Abbas |
2013-03-14 |
NULL |
Pacific Sales Manager |
106 |
Production |
Kim |
Abercrombie |
2010-01-16 |
NULL |
Production Technician - WC60 |
144 |
Quality Assurance |
Hazem |
Abolrous |
2009-02-28 |
NULL |
Quality Assurance Manager |
155 |
Shipping and Receiving |
Pilar |
Ackerman |
2009-01-02 |
NULL |
Shipping and Receiving Supervisor |
156 |
Production |
Jay |
Adams |
2009-03-05 |
NULL |
Production Technician - WC60 |
154 |
Information Services |
François |
Ajenstat |
2009-01-17 |
NULL |
Database Administrator |
156 |
Sales |
Amy |
Alberts |
2012-04-16 |
NULL |
European Sales Manager |
117 |
Production |
Greg |
Alderson |
2008-12-02 |
NULL |
Production Technician - WC45 |
157 |
Quality Assurance |
Sean |
Alexander |
2008-12-28 |
NULL |
Quality Assurance Technician |
157 |
Facilities and Maintenance |
Gary |
Altman |
2009-12-02 |
NULL |
Facilities Manager |
145 |
Multiple return clauses¶
In the following sample there is more than one return clause, this is because depending on the situation it is not necessary to keep executing the whole function.
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
CREATE OR ALTER FUNCTIONcreate_new_team(@team_name VARCHAR(50))
</strong>RETURNS @new_team TABLE (type VARCHAR(50), name VARCHAR(50))
AS
BEGIN
DECLARE @employees INT
SET @employees = (SELECT count(*) FROM employee)
DECLARE @type VARCHAR(15)
SET @type = 'small_team'
IF (@employees < 8)
BEGIN
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SET @type = 'big_team'
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SELECT * FROMcreate_new_team('Team1');
Result¶
TYPE |
NAME |
---|---|
SMALL_TEAM |
TEAM1 |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTIONcreate_new_team (TEAM_NAME STRING)
RETURNS NEW_TEAM TABLE (
type VARCHAR(50),
name VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @employees INT
SET @employees = (SELECT count(*) FROM employee)
DECLARE @type VARCHAR(15)
SET @type = 'small_team'
IF (@employees < 8)
BEGIN
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SET @type = 'big_team'
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SELECT * FROMcreate_new_team('Team1');;
Result¶
TYPE |
NAME |
---|---|
SMALL_TEAM |
TEAM1 |
Warning
This transformation is applied when there is only one value to insert, if there is more than one value it is necessary to use a stored procedure.
Complex cases¶
The example is a complex case that uses nested if
statements and inserts a value depending on the true condition.
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
CREATE OR ALTER FUNCTION vacation_status(@id VARCHAR (50))
RETURNS @status TABLE (vacation_status VARCHAR(30))
AS
BEGIN
DECLARE @hire_date DATETIME
SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
DECLARE @vacation_hours INT
SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
DECLARE @time_working INT
SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))
IF (@vacation_hours > 0)
BEGIN
IF (@time_working > 3)
BEGIN
IF (@vacation_hours < 120)
BEGIN
INSERT INTO @status VALUES ('Ok')
END
IF (@vacation_hours = 120)
BEGIN
INSERT INTO @status values ('In the limit')
END
IF (@vacation_hours > 120)
BEGIN
INSERT INTO @status VALUES ('With excess')
END
END
ELSE
BEGIN
INSERT INTO @status values ('Hired recently')
END
END
ELSE
BEGIN
INSERT INTO @status values ('No hours')
END
RETURN
END
SELECT * FROM vacation_status('adventure-worksken0')
Result¶
VACATION_STATUS |
---|
OK |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION vacation_status (ID STRING)
RETURNS STATUS TABLE (
vacation_status VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @hire_date DATETIME
SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
DECLARE @vacation_hours INT
SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
DECLARE @time_working INT
SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))
IF (@vacation_hours > 0)
BEGIN
IF (@time_working > 3)
BEGIN
IF (@vacation_hours < 120)
BEGIN
INSERT INTO @status VALUES ('Ok')
END
IF (@vacation_hours = 120)
BEGIN
INSERT INTO @status values ('In the limit')
END
IF (@vacation_hours > 120)
BEGIN
INSERT INTO @status VALUES ('With excess')
END
END
ELSE
BEGIN
INSERT INTO @status values ('Hired recently')
END
END
ELSE
BEGIN
INSERT INTO @status values ('No hours')
END
RETURN
END
SELECT * FROM vacation_status('adventure-worksken0');
Second Tab¶
VACATION_STATUS |
---|
OK |
Known Issues¶
While statements along side queries¶
The problem with this example is that there’s no way of transforming the while statement to a CTE inside the WITH
clause of the main select, this forces us to transform this statement to store procedure to maintain the same logic.
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
--Additional Params: -t JavaScript
CREATE OR ALTER FUNCTION get_group_name
(@department_id INT)
RETURNS @group_names TABLE (group_name VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'
WHILE @name = 'Another Department'
BEGIN
IF (@department_id < 3)
BEGIN
SET @name = 'engineering'
END
IF @department_id = 3
BEGIN
SET @name = 'Tool Design'
END
SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END
SELECT * FROM get_group_name(9);
Result¶
GROUP_NAME |
---|
Tool Design |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_group_name
(DEPARTMENT_ID INT)
RETURNS @group_names TABLE (
group_name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'
WHILE @name = 'Another Department'
BEGIN
IF (@department_id < 3)
BEGIN
SET @name = 'engineering'
END
IF @department_id = 3
BEGIN
SET @name = 'Tool Design'
END
SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END
SELECT * FROM get_group_name(9);;
Result¶
GROUP_NAME |
---|
Tool Design |
Declare Cursor¶
User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR
. Use a Stored Procedure to work with cursors.
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
--Additional Params: -t JavaScript
CREATE OR ALTER FUNCTION amount_new_specimens(@id int)
RETURNS @new_specimens TABLE (amount int)
AS
BEGIN
DECLARE @first_specimen VARCHAR(30) ;
set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
DECLARE @second_specimen VARCHAR(30);
DECLARE @specimens TABLE (name_specimen VARCHAR(30))
DECLARE Cursor1 CURSOR
FOR SELECT name_specimen
FROM specimen
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @second_specimen;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @first_specimen <> @second_specimen
BEGIN
INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
END
FETCH NEXT FROM cursor1
INTO @second_specimen;
END
CLOSE cursor1;
DEALLOCATE cursor1;
INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
RETURN
END
SELECT * FROM amount_new_specimens(1);
Result¶
AMOUNT |
---|
3 |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
--Additional Params: -t JavaScript
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION amount_new_specimens (ID INT)
RETURNS @new_specimens TABLE (
amount INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @first_specimen VARCHAR(30) ;
set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
DECLARE @second_specimen VARCHAR(30);
DECLARE @specimens TABLE (name_specimen VARCHAR(30))
DECLARE Cursor1 CURSOR
FOR SELECT name_specimen
FROM specimen
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @second_specimen;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @first_specimen <> @second_specimen
BEGIN
INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
END
FETCH NEXT FROM cursor1
INTO @second_specimen;
END
CLOSE cursor1;
DEALLOCATE cursor1;
INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
RETURN
END
SELECT * FROM amount_new_specimens(1);;
Result¶
AMOUNT |
---|
3 |
Different statements are not supported in Common Tables Expressions¶
The clauses UPDATE
, INSERT
, DELETE
, ALTER
or DROP
are not supported on the body of common tables expressions, even after their declaration using a delimitator. For this reason, the function can be modified to work as a stored procedure.
Transact-SQL¶
MULTI-STATEMENT TABLE-VALUED¶
--Additional Params: -t JavaScript
CREATE OR ALTER PROCEDURE product_history
AS
BEGIN
DECLARE @product_history TABLE (
product_name NVARCHAR(50),
rating INT
)
INSERT INTO @product_history
SELECT P.Name AS product_name, AVG(ALL R.rating) FROM Production.product P
INNER JOIN Production.product_review R
ON R.product_ID = P.product_ID
GROUP BY P.Name;
DELETE FROM @product_history
WHERE rating < 2;
SELECT * FROM @product_history;
END
GO;
EXEC product_history
Result¶
PRODUCT_NAME |
Rating |
---|---|
HL Mountain Pedal |
3 |
Mountain Bike Socks, M |
5 |
Road-550-W Yellow, 40 |
5 |
Snowflake SQL¶
MULTI-STATEMENT TABLE-VALUED¶
CREATE OR REPLACE PROCEDURE product_history ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert AI Helpers Code
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
function* sqlsplit(sql) {
var part = '';
var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
for(var i = 0;i < sql.length;i++) {
if (sql[i] == ';') {
yield part + sql[i];
part = '';
} else if (ismark()) {
part += sql[i++] + sql[i++];
while ( i < sql.length && !ismark() ) {
part += sql[i++];
}
part += sql[i] + sql[i++];
} else part += sql[i];
}
if (part.trim().length) yield part;
};
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
binds = binds ? binds.map(fixBind) : binds;
for(var stmt of sqlsplit(stmt)) {
try {
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
return {
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
};
} catch(error) {
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
MESSAGE_TEXT = error.message.toString();
SQLCODE = error.code.toString();
SQLSTATE = error.state.toString();
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
});
throw error;
}
}
};
// END REGION
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_product_history (
product_name VARCHAR(50),
rating INT
)`);
EXEC(` INSERT INTO T_product_history
SELECT
P.Name AS product_name,
AVG(ALL R.rating) FROM
Production.product P
INNER JOIN
Production.product_review R
ON R.product_ID = P.product_ID
GROUP BY
P.Name`);
EXEC(`DELETE FROM
T_product_history
WHERE
rating < 2`);
EXEC(`
SELECT
*
FROM
T_product_history`);
$$;
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
;
CALL product_history();
Result¶
PRODUCT_NAME |
Rating |
---|---|
HL Mountain Pedal |
3 |
Mountain Bike Socks, M |
5 |
Road-550-W Yellow, 40 |
5 |
Related EWIs¶
SSC-EWI-0040: Statement Not Supported.
SSC-EWI-0073: Pending Functional Equivalence Review
SCALAR¶
Translation reference to convert Transact-SQL UDF (User Defined Functions) with scalar return type to Snowflake.
Applies to
SQL Server
Azure Synapse Analytics
Description¶
Note
Some parts in the output code are omitted for clarity reasons.
A scalar 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 scalar value. (SQL Server Language ReferenceCREATE FUNCTION subsection).
Note
These functions are usually used inside the SELECT
statement, or single variable setup (most likely inside a stored procedure).
Transact-SQL Syntax¶
-- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Snowflake Syntax¶
Snowflake allows 3 different languages in their user defined functions:
SQL
JavaScript
Java
For now, SnowConvert AI will support only SQL
and JavaScript
as target languages.
SQL¶
Note
SQL user defined functions only supports one query as their body. They can read from the database, but is not allowed to write or modify it. (Scalar SQL UDFs Reference).
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ COMMENT = '<string_literal>' ]
AS '<function_definition>'
JavaScript¶
Note
JavaScript user defined functions allows multiple statements in their bodies, but cannot perform queries to the database. (Scalar JavaScript UDFs Reference)
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
LANGUAGE JAVASCRIPT
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ COMMENT = '<string_literal>' ]
AS '<function_definition>'
Sample Source Patterns¶
Set and Declare Statements¶
The most common statements in function bodies are the DECLARE
and SET
statements. For DECLARE
statements without default value, the transformation will be ignored. SET
statements and DECLARE
statements with a default value, will be transformed to a COMMON TABLE EXPRESSION.
Each common table expression will contain a column that represents the local variable value.
Transact-SQL¶
Query¶
CREATE OR ALTER FUNCTION PURCHASING.GetVendorName()
RETURNS NVARCHAR(50) AS
BEGIN
DECLARE @result NVARCHAR(50)
DECLARE @BUSINESSENTITYID INT
SET @BUSINESSENTITYID = 1492
SELECT @result = Name FROM PURCHASING.VENDOR WHERE BUSINESSENTITYID = @BUSINESSENTITYID
RETURN @result
END
GO
SELECT PURCHASING.GetVendorName() as vendor_name;
Result¶
vendor_name |
---|
Australia Bike Retailer |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION PURCHASING.GetVendorName ()
RETURNS VARCHAR(50)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
1492 AS BUSINESSENTITYID
),
CTE2 AS
(
SELECT
Name AS RESULT
FROM
PURCHASING.VENDOR
WHERE
BUSINESSENTITYID = (
SELECT
BUSINESSENTITYID
FROM
CTE1
)
)
SELECT
RESULT
FROM
CTE2
$$;
SELECT
PURCHASING.GetVendorName() as vendor_name;
Result¶
VENDOR_NAME |
---|
Australia Bike Retailer |
If/Else Statement Transformation¶
If/Else statement can be handled in different ways, they can be either transformed to javascript or to SQL using the CASE EXPRESSION inside the select allowing conditionals inside the queries, while the javascript transformation is pretty straightforward, the Case statement might not be so obvious at first glance.
Transact-SQL¶
Query¶
CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @result VARCHAR(10)
DECLARE @ActiveFlag BIT
SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
IF @ActiveFlag = 1
SET @result = 'YES'
ELSE IF @ActiveFlag = 0
SET @result = 'NO'
RETURN @result
END
GO
SELECT PURCHASING.HasActiveFlag(1516) as has_active_flag;
Result¶
has_active_flag |
---|
NO |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (P_BUSINESSENTITYID INT)
RETURNS VARCHAR(10)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
ActiveFlag AS ACTIVEFLAG
from
PURCHASING.VENDOR v
where
v.BUSINESSENTITYID = P_BUSINESSENTITYID
),
CTE2 AS
(
SELECT
CASE
WHEN (
SELECT
ACTIVEFLAG
FROM
CTE1
) = 1
THEN 'YES'
WHEN (
SELECT
ACTIVEFLAG
FROM
CTE1
) = 0
THEN 'NO'
END AS RESULT
)
SELECT
RESULT
FROM
CTE2
$$;
SELECT
PURCHASING.HasActiveFlag(1516) as has_active_flag;
Result¶
HAS_ACTIVE_FLAG |
---|
NO |
Nested Statements¶
For nested statements, the structured programming is being transformed to a single query. The statements in the control-of-flow are going to be nested in table structures in order to preserve the execution order.
Note
CASE EXPRESSIONS
only can return one value per statement
Example¶
Note
The following code in both programming paradigms is functionally equivalent.
Structured Programming¶
DECLARE @VendorId AS int;
DECLARE @AccountNumber AS VARCHAR(50);
SELECT @VendorId = poh.VendorID
FROM Purchasing.PurchaseOrderHeader poh
WHERE PurchaseOrderID = 1
SELECT @AccountNumber = v.AccountNumber
FROM Purchasing.Vendor v
WHERE v.BusinessEntityID = @VendorId
SQL¶
SELECT V.AccountNumber AccountNumber
FROM (SELECT poh.VendorID VendorId
FROM Purchasing.PurchaseOrderHeader poh
WHERE PurchaseOrderID = 1
) T1, Purchasing.Vendor v
WHERE v.BusinessEntityID = T1.VendorId
Result¶
AccountNumber |
---|
LITWARE0001 |
Conditional variables through SELECTs¶
Variable definition and assignment within conditional statements tends to be somewhat problematic, because references to the variable further down the code would have to know where the variable was last modified. Not only that, but if the reference is within another conditional statement, then there would have to be some kind of redirect that references the previous known assignment to the variable.
This is all aggravated by nesting and complex querying that can be found on input code. That’s why a specific EWI is added when these patterns are found.
In the following scenario, the first IF
statement can be transformed without problems, because the contents are straightforward enough. The second and third IF
statements are commented out because they’re not supported at the moment, since there are statements other than variable assignments through SELECT
.
SQL Server¶
Query¶
CREATE or ALTER FUNCTION PURCHASING.SELECTINUDF (
@param1 varchar(12)
)
RETURNS int
AS
BEGIN
declare @var1 int;
declare @var2 int;
declare @var3 int;
IF @param1 = 'first'
BEGIN
select @var1 = col1 + 10 from table1 WHERE id = 0;
select @var2 = col1 + 20 from table1 WHERE id = 0;
select @var3 = col1 + 30 from table1 WHERE id = 0;
END
IF @param1 = 'second'
BEGIN
declare @var4 int = 10;
select @var1 = col1 + 40 from table1 WHERE id = 0;
select @var2 = col1 + 40 from table1 WHERE id = 0;
END
IF @param1 = 'third'
BEGIN
select col1 from table1 where id = 0;
select @var1 = col1 + 50 from table1 WHERE id = 0;
select @var2 = col1 + 50 from table1 WHERE id = 0;
END
RETURN @var1
END
SELECT PURCHASING.SELECTINUDF('first') as result; -- Assuming table1.col1 is 0 when ID = 0
Result¶
RESULT |
---|
10 |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION PURCHASING.SELECTINUDF (PARAM1 STRING)
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
CASE
WHEN PARAM1 = 'first'
THEN (SELECT
col1 + 10 AS VAR1 from
table1
WHERE
id = 0)
END AS VAR1,
CASE
WHEN PARAM1 = 'first'
THEN (SELECT
col1 + 20 AS VAR2 from
table1
WHERE
id = 0)
END AS VAR2,
CASE
WHEN PARAM1 = 'first'
THEN (SELECT
col1 + 30 AS VAR3 from
table1
WHERE
id = 0)
END AS VAR3
),
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
CTE2 AS
(
/* IF @param1 = 'second'
BEGIN
declare @var4 int = 10;
select @var1 = col1 + 40 from table1 WHERE id = 0;
select @var2 = col1 + 40 from table1 WHERE id = 0;
END*/
SELECT
null
),
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
CTE3 AS
(
/* IF @param1 = 'third'
BEGIN
select col1 from table1 where id = 0;
select @var1 = col1 + 50 from table1 WHERE id = 0;
select @var2 = col1 + 50 from table1 WHERE id = 0;
END*/
SELECT
null
),
CTE4 AS
(
SELECT
PURCHASING.SELECTINUDF('first') as result
)
SELECT
VAR1
FROM
CTE4
$$ -- Assuming table1.col1 is 0 when ID = 0
;
Result¶
RESULT |
---|
10 |
Assign and return a variable¶
In this simple pattern, there is a variable declaration, then, that variable is set using a SELECT
statement and finally returned. This is going to be migrated to a Common Table Expression in order to keep the original behavior.
SQL Server¶
Query¶
CREATE OR ALTER FUNCTION Purchasing.GetTotalFreight()
RETURNS MONEY AS
BEGIN
DECLARE @Result MONEY
SELECT @Result = ISNULL(SUM(t.Freight), 0) from Purchasing.PurchaseOrderHeader t
return @Result
END
GO
select Purchasing.GetTotalFreight() as Result;
Result¶
Result |
---|
1583978.2263 |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION Purchasing.GetTotalFreight ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
NVL(SUM(t.Freight), 0) AS RESULT from
Purchasing.PurchaseOrderHeader t
)
SELECT
RESULT
FROM
CTE1
$$;
select
Purchasing.GetTotalFreight() as Result;
Result¶
RESULT |
---|
1583978.2263 |
Multiple Function Calls¶
For this specific pattern there are no obvious queries, but there are multiple calls to multiple functions working on the same variable and returning it at the end. Since Snowflake only supports queries inside its functions, the solution for this block is going to be adding it to a Select and nesting the calls inside, making sure the return value is the same as the one on the source.
SQL Server¶
Query¶
CREATE OR ALTER FUNCTION PURCHASING.Foo
(
@PARAM1 INT
)
RETURNS varchar(25)
AS
BEGIN
DECLARE @filter INT = @PARAM1
DECLARE @NAME VARCHAR(25) = (SELECT Name from Purchasing.Vendor v where BusinessEntityID = @filter)
SET @NAME = REPLACE(@NAME, 'Australia', 'USA')
SET @NAME = REPLACE(@NAME, 'Bike', 'Car')
RETURN @NAME
END
GO
SELECT PURCHASING.Foo(1492) AS Name;
Result¶
Name |
---|
USA Car Retailer |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION PURCHASING.Foo (PARAM1 INT)
RETURNS VARCHAR(25)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
PARAM1 AS FILTER
),
CTE2 AS
(
SELECT
(SELECT
Name
from
Purchasing.Vendor v
where
BusinessEntityID = (
SELECT
FILTER
FROM
CTE1
)
) AS NAME
),
CTE3 AS
(
SELECT
REPLACE((
SELECT
NAME
FROM
CTE3
), 'Australia', 'USA') AS NAME
),
CTE4 AS
(
SELECT
REPLACE((
SELECT
NAME
FROM
CTE4
), 'Bike', 'Car') AS NAME
)
SELECT
NAME
FROM
CTE4
$$;
SELECT
PURCHASING.Foo(1492) AS Name;
Result¶
NAME |
---|
USA Car Retailer |
Increase a variable based on multiple IF conditions and return its value¶
For this pattern, a variable is modified (increased in this case) using multiple IF conditions. In the beginning, a set of variables is initialized and used to determine whether the result variable should be increased or not. Finally, the result variable is returned.
SQL Server¶
Query¶
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS MONEY
AS
BEGIN
declare @firstValue MONEY
declare @secondValue MONEY
declare @Result MONEY
select @Result = 0
select @firstValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 1
select @secondValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 2
if @firstValue is not null
select @Result = @Result + @firstValue
if @secondValue is not null
select @Result = @Result + @secondValue
return @Result
END
GO
SELECT PURCHASING.Foo() AS Result;
Result¶
Result |
---|
473.1415 |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
select
0 AS RESULT
),
CTE2 AS
(
select
SubTotal AS FIRSTVALUE
from
Purchasing.PurchaseOrderHeader
where
PurchaseOrderID = 1
),
CTE3 AS
(
select
SubTotal AS SECONDVALUE
from
Purchasing.PurchaseOrderHeader
where
PurchaseOrderID = 2
),
CTE4 AS
(
SELECT
CASE
WHEN (
SELECT
FIRSTVALUE
FROM
CTE2
) is not null
THEN (
select
(
SELECT
RESULT
FROM
CTE1
) + (
SELECT
FIRSTVALUE
FROM
CTE2
) AS RESULT)
END AS RESULT
),
CTE5 AS
(
SELECT
CASE
WHEN (
SELECT
SECONDVALUE
FROM
CTE3
) is not null
THEN (
select
(
SELECT
RESULT
FROM
CTE1
) + (
SELECT
SECONDVALUE
FROM
CTE3
) AS RESULT)
ELSE (SELECT
RESULT
FROM
CTE4)
END AS RESULT
)
SELECT
RESULT
FROM
CTE5
$$;
SELECT
PURCHASING.Foo() AS Result;
Result¶
RESULT |
---|
473.1415 |
Two or more RETURN statements¶
For this pattern, the IF
block containing the return clause that breaks the code flow is added at the end of the body, like the final statement to be executed in a CASE
expression.
Basic Case¶
For this particular scenario, there is no logic between the conditional RETURN
statement and the final RETURN
statement, so all body will be mapped to a single CASE EXPRESSION
.
SQL Server¶
Query¶
CREATE OR ALTER FUNCTION [PURCHASING].[FOO] ()
RETURNS INT
AS
BEGIN
IF exists (SELECT PreferredVendorStatus FROM Purchasing.Vendor v )
RETURN 1
RETURN 0
END
GO
SELECT PURCHASING.FOO() as result;
Result¶
result |
---|
1 |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
SELECT
CASE
WHEN exists (SELECT
PreferredVendorStatus
FROM
Purchasing.Vendor v
)
THEN 1
ELSE 0
END
$$;
SELECT
PURCHASING.FOO() as result;
Result¶
RESULT |
---|
1 |
Common Table Expressions¶
Common table expressions will be kept as in the original code, and they are going to be concatenated with the generated ones. SnowConvert AI is able to identify first all the original COMMON TABLE EXPRESSION
names in order to avoid generating duplicated names.
SQL Server¶
Query¶
CREATE OR ALTER FUNCTION [PURCHASING].[FOO]
(
@status INT
)
Returns INT
As
Begin
Declare @result as int = 0
;WITH ctetable(RevisionNumber) as
(
SELECT RevisionNumber
FROM Purchasing.PurchaseOrderHeader poh
where poh.Status = @status
),
finalCte As
(
SELECT RevisionNumber FROM ctetable
)
Select @result = count(RevisionNumber) from finalCte
return @result;
End
GO
SELECT PURCHASING.FOO(4) as result;
Result¶
result |
---|
3689 |
Snowflake¶
Query¶
CREATE OR REPLACE FUNCTION PURCHASING.FOO (STATUS INT)
Returns INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
0 AS RESULT
),
ctetable (
RevisionNumber
) as
(
SELECT
RevisionNumber
FROM
Purchasing.PurchaseOrderHeader poh
where
poh.Status = STATUS
),
finalCte As
(
SELECT
RevisionNumber
FROM
ctetable
),
CTE2 AS
(
Select
COUNT(RevisionNumber) AS RESULT from
finalCte
)
SELECT
RESULT
FROM
CTE2
$$;
SELECT
PURCHASING.FOO(4) as result;
Result¶
RESULT |
---|
3689 |
Transform to JavaScript UDFs¶
If there are multiple statements and the function does not access the database in any way, it can be transformed into a JavaScript function keeping the functional equivalence
SQL Server¶
Query 1¶
CREATE OR ALTER FUNCTION PURCHASING.GetFiscalYear
(
@DATE AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @FiscalYear AS INT
DECLARE @CurMonth AS INT
SET @CurMonth = DATEPART(M,@DATE)
SET @FiscalYear = DATEPART(YYYY, @DATE)
IF (@CurMonth >= 7)
BEGIN
SET @FiscalYear = @FiscalYear + 1
END
RETURN @FiscalYear
END
GO
SELECT PURCHASING.GetFiscalYear('2020-10-10') as DATE;
Query 2¶
CREATE OR ALTER FUNCTION PURCHASING.[getCleanChargeCode]
(
@ChargeCode varchar(50)
)
returns varchar(50) as
begin
declare @CleanChargeCode varchar(50),@Len int,@Pos int=2
set @Pos=LEN(@ChargeCode)-1
while @Pos > 1
begin
set @CleanChargeCode=RIGHT(@ChargeCode,@Pos)
if TRY_CAST(@CleanChargeCode as bigint) is not null
return @CleanChargeCode
set @Pos=@Pos-1
end
set @Pos=LEN(@ChargeCode)-1
while @Pos > 1
begin
set @CleanChargeCode=LEFT(@ChargeCode,@Pos)
if TRY_CAST(@CleanChargeCode as bigint) is not null
return @CleanChargeCode
set @Pos=@Pos-1
end
return null
end
GO
SELECT PURCHASING.[getCleanChargeCode]('16test') AS CleanChargeCode;
Result 1¶
DATE |
---|
2021 |
Result 2¶
CleanChargeCode |
---|
16 |
Snowflake¶
Query 1¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE PURCHASING.GetFiscalYear (DATE TIMESTAMP_NTZ(3))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "07/11/2025", "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
FISCALYEAR INT;
CURMONTH INT;
BEGIN
CURMONTH := DATE_PART(month, :DATE :: TIMESTAMP);
FISCALYEAR := DATE_PART(year, :DATE :: TIMESTAMP);
IF ((:CURMONTH >= 7)) THEN
BEGIN
FISCALYEAR := :FISCALYEAR + 1;
END;
END IF;
RETURN :FISCALYEAR;
END;
$$;
SELECT
PURCHASING.GetFiscalYear('2020-10-10') !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as DATE;
Query 2¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE PURCHASING.getCleanChargeCode (CHARGECODE STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "07/16/2025", "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
CLEANCHARGECODE VARCHAR(50);
LEN INT;
POS INT := 2;
BEGIN
POS := LEN(:CHARGECODE)-1;
WHILE (:POS > 1) LOOP
CLEANCHARGECODE := RIGHT(:CHARGECODE, :POS);
IF (CAST(:CLEANCHARGECODE AS BIGINT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/!!!RESOLVE EWI!!! /*** SSC-EWI-TS0074 - CAST RESULT MAY BE DIFFERENT FROM TRY_CAST FUNCTION DUE TO MISSING DEPENDENCIES ***/!!! is not null) THEN
RETURN :CLEANCHARGECODE;
END IF;
POS := :POS -1;
END LOOP;
POS := LEN(:CHARGECODE)-1;
WHILE (:POS > 1) LOOP
CLEANCHARGECODE := LEFT(:CHARGECODE, :POS);
IF (CAST(:CLEANCHARGECODE AS BIGINT) /*** SSC-FDM-TS0005 - TRY_CONVERT/TRY_CAST COULD NOT BE CONVERTED TO TRY_CAST ***/!!!RESOLVE EWI!!! /*** SSC-EWI-TS0074 - CAST RESULT MAY BE DIFFERENT FROM TRY_CAST FUNCTION DUE TO MISSING DEPENDENCIES ***/!!! is not null) THEN
RETURN :CLEANCHARGECODE;
END IF;
POS := :POS -1;
END LOOP;
RETURN null;
END;
$$;
SELECT
PURCHASING.getCleanChargeCode('16test') !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! AS CleanChargeCode;
Result 1¶
DATE |
---|
2021.0 |
Result 2¶
CLEANCHARGECODE |
---|
16 |
Known Issues¶
Warning
User-defined functions cannot be used to perform actions that modify the database state
Warning
User-defined functions cannot contain an OUTPUT INTO
clause that has a table as its target
Warning
User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR
. Use a Stored Procedure if you need to use cursors.
Warning
User-defined functions cannot perform control-of-flow statements such as WHILE if there is at least one call to the database
Warning
User-defined functions with references to other user-defined functions that were transformed to Stored Procedures, will be transformed to Stored Procedures too.
Warning
User-defined functions that use @@ROWCOUNT are not supported in SQL and should be transformed to stored procedures in order to keep the functional equivalence.
Warning
User-defined functions that have SELECT
statements assigning a variable to itself is not supported in Snowflake. See also SELECT @local_variable
For all the unsupported cases, please check the related EWIs and the patterns below to obtain recommendations and possible workarounds.
Conditionals other than if/else statements along side queries¶
The next scenario involves the use of the “while statement” along side other queries. The problem with this example is that there’s no way of transforming the while statement to a CTE inside the WITH
clause of the main select, this forces us to transform this statement to JavaScript procedure to maintain the same logic.
SQL Server¶
Query¶
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
DECLARE @i int = 0, @p int;
Select @p = COUNT(*) FROM PURCHASING.VENDOR
WHILE (@p < 1000)
BEGIN
SET @i = @i + 1
SET @p = @p + @i
END
IF (@i = 6)
RETURN 1
RETURN @p
END
GO
SELECT PURCHASING.FOO() as result;
Result¶
result |
---|
1007 |
Snowflake
Query¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "07/11/2025", "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
I INT := 0;
P INT;
BEGIN
Select
COUNT(*)
INTO
:P
FROM
PURCHASING.VENDOR;
WHILE (:P < 1000) LOOP
I := :I + 1;
P := :P + :I;
END LOOP;
IF ((:I = 6)) THEN
RETURN 1;
END IF;
RETURN :P;
END;
$$;
SELECT
PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as result;
Result¶
FOO |
---|
1007 |
Assign a variable using its own value iterating through a rowset¶
In the following example, the variable @names
is used to concatenate multiple values from a column into one single string. The variable is updated on each iteration as shown, which is not supported by SnowFlake UDFs. For this scenario, the function should be transformed into a procedure.
SQL Server
Query¶
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @names varchar(8000)
SET @names = ''
SELECT @names = ISNULL(@names + ' ', '') + Name from Purchasing.Vendor v
return @names
END
GO
select PURCHASING.FOO() as names;
Result¶
names |
---|
Australia Bike Retailer Allenson Cycles Advanced Bicycles Trikes, Inc. Morgan Bike Accessories Cycling Master Chicago Rent-All Greenwood Athletic Company Compete Enterprises, Inc International Light Speed Training Systems Gardner Touring Cycles Internati |
Snowflake query
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "07/11/2025", "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
NAMES VARCHAR(8000);
BEGIN
NAMES := '';
SELECT
NVL(:NAMES || ' ', '') + Name
INTO
:NAMES
from
Purchasing.Vendor v;
RETURN :NAMES;
END;
$$;
select
PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as names;
Warning
For the described scenarios above, consider the following limitations:
All the calls to user-defined functions in DML queries such as
SELECT
,INSERT
,DELETE
,UPDATE
orMERGE
will fail because calls to Stored Procedures within these queries are not allowed.Calls to user-defined functions inside procedures, should be preceeded by the
CALL
keyword.Use- defined functions used in COMPUTED COLUMNS will fail during the execution.
Related EWIs¶
SSC-EWI-0067: UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.
SSC-EWI-0068: User defined function was transformed to a Snowflake procedure.
SSC-EWI-0073: Pending Functional Equivalence Review.