MSCEWI2067

Multiple result sets are returned in temporary tables

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Snowflake Scripting procedures only allow one result set to be returned per procedure.

To replicate Teradata behavior, when there are two or more result sets to return, they are stored in temporary tables. The Snowflake Scripting procedure will return an array containing the name of the temporary tables.

Example code

Input Code:

REPLACE MACRO sampleMacro AS 
(
    SELECT CURRENT_DATE AS DT;
    SELECT CURRENT_DATE AS DT_TWO;
);

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.SampleMacro()
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        return_arr ARRAY := array_construct();
        tbl_nm VARCHAR;
    BEGIN
        tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
            SELECT CURRENT_DATE AS DT;
        return_arr := array_append(return_arr, :tbl_nm);
        
	tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
	CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
	    SELECT CURRENT_DATE AS DT_TWO;
	return_arr := array_append(return_arr, :tbl_nm);
	
	--** MSC-WARNING - MSCEWI2067 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
	RETURN return_arr;
    END;
$$;

Recommendations

• To obtain the result sets, it is necessary to run a SELECT query with the name of the temporary tables returned by the procedure.

• As much as possible, avoid procedures that return multiple result sets; instead, make them single-responsibility for more direct results.

• If you need more support, you can email us at [email protected]