Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.
Some parts in the output code are omitted for clarity reasons.
All the code samples on this page have not been implemented yet in SnowConvert. 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
SQL Server 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 of CREATE 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
SQL Server
IN -> SqlServer_01.sql
CREATE OR ALTER FUNCTION calc_behavioral_segment()RETURNS @behavioral_segments TABLE (behavioral_segment VARCHAR(50))ASBEGIN DECLARE @col varchar(15) SET @col ='Unknown' INSERT INTO @behavioral_segments SELECT @col RETURN ENDSELECT * FROM calc_behavioral_segment();
BEHAVIORAL_SEGMENT|
------------------+
Unknown|
Snowflake SQL
OUT -> SqlServer_01.sql
!!!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"}}'ASBEGIN DECLARE @col varchar(15) SET @col ='Unknown' INSERT INTO @behavioral_segments SELECT @col RETURNENDSELECT * FROM calc_behavioral_segment();;
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
SQL Server
IN -> SqlServer_02.sql
CREATE OR ALTER FUNCTION odd_or_even_number(@number INT)RETURNS @numbers TABLE (number_type VARCHAR(15))ASBEGIN IF ((@number % 2) =0) BEGIN INSERT @numbers SELECT 'Even' END ELSE BEGIN INSERT @numbers SELECT 'Odd' END RETURNENDSELECT * FROM odd_or_even_number(9);
NUMBER_TYPE|
------------------+
Odd|
Snowflake SQL
OUT -> SqlServer_02.sql
!!!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"}}'ASBEGIN IF ((@number % 2) =0) BEGIN INSERT @numbers SELECT 'Even' END ELSE BEGIN INSERT @numbers SELECT 'Odd' END RETURNENDSELECT * FROM odd_or_even_number(9);;
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
SQL Server
IN -> SqlServer_03.sql
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)) ASBEGIN 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) RETURNENDSELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');
!!!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"}}'ASBEGIN 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) RETURNENDSELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');;
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.
SQL Server
IN -> SqlServer_04.sql
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();
!!!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();;
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.
SQL Server
IN -> SqlServer_05.sql
CREATE OR ALTER FUNCTION create_new_team(@team_name VARCHAR(50))RETURNS @new_team TABLE (type VARCHAR(50), name VARCHAR(50))ASBEGIN 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) RETURNENDSELECT * FROM create_new_team('Team1');
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.
SQL Server
IN -> SqlServer_06.sql
CREATE OR ALTER FUNCTION vacation_status(@id VARCHAR (50))RETURNS @status TABLE (vacation_status VARCHAR(30))ASBEGIN 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 RETURNENDSELECT * FROM vacation_status('adventure-worksken0')
VACATION_STATUS|
-----------------+
OK|
Snowflake SQL
OUT -> SqlServer_06.sql
!!!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"}}'ASBEGIN 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 RETURNENDSELECT * FROM vacation_status('adventure-worksken0');
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.
SQL Server
IN -> SqlServer_07.sql
--Additional Params: -t JavaScriptCREATE OR ALTER FUNCTION get_group_name(@department_id INT)RETURNS @group_names TABLE (group_name VARCHAR(15))ASBEGINDECLARE @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 /3ENDINSERT @group_names SELECT @nameRETURNENDSELECT * FROM get_group_name(9);
GROUP_NAME|
------------------+
Tool Design|
Snowflake SQL
OUT -> SqlServer_07.sql
!!!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"}}'ASBEGINDECLARE @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 /3ENDINSERT @group_names SELECT @nameRETURNENDSELECT * FROM get_group_name(9);;
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.
SQL Server
IN -> SqlServer_08.sql
--Additional Params: -t JavaScriptCREATE OR ALTER FUNCTION amount_new_specimens(@id int)RETURNS @new_specimens TABLE (amount int)ASBEGIN 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 ENDSELECT * FROM amount_new_specimens(1);
AMOUNT|
------------------+
3|
Snowflake SQL
OUT -> SqlServer_08.sql
--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"}}'ASBEGIN 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 RETURNENDSELECT * FROM amount_new_specimens(1);;
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.
SQL Server
IN -> SqlServer_09.sql
--Additional Params: -t JavaScriptCREATE OR ALTER PROCEDURE product_history ASBEGIN 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; ENDGO;EXEC product_history