MULTI-STATEMENT TABLE-VALUED

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

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

MULTI-STATEMENT TABLE-VALUED

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

Result

BEHAVIORAL_SEGMENT|
------------------+
           Unknown|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

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"}}'
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

SQL Server

MULTI-STATEMENT TABLE-VALUED

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

Result

       NUMBER_TYPE|
------------------+
               Odd|

Snowflake SQL

MULTI-STATEMENT TABLE-VALUED

OUT -> SqlServer_02.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!