MULTI-STATEMENT TABLE-VALUED

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

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.

Description

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))
AS
BEGIN
	DECLARE @col varchar(15)
	SET @col = 'Unknown'
	INSERT INTO @behavioral_segments 
	SELECT @col
	
	RETURN 
END

SELECT * FROM calc_behavioral_segment();

Snowflake SQL

OUT -> SqlServer_01.sql
CREATE OR REPLACE FUNCTION calc_behavioral_segment()
RETURNS TABLE(behavioral_segment VARCHAR(50))
AS
$$     
     WITH CTE AS (SELECT 'Unknown' as behavior)
     SELECT behavior FROM CTE
$$;

SELECT * FROM TABLE(calc_behavioral_segment());

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

Snowflake SQL

OUT -> SqlServer_02.sql
CREATE OR REPLACE FUNCTION odd_or_even_number(number int)
RETURNS TABLE(number_type VARCHAR(50))
AS
$$
     WITH CTE2 as (SELECT CASE WHEN (number % 2) = 0 THEN 'EVEN'
     ElSE 'ODD'
     END AS result)
     SELECT result FROM CTE2
$$;

select * from table(odd_or_even_number(9));

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