SSC-FDM-0020

Multiple result sets are returned in temporary tables

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 (Teradata):

IN -> Teradata_01.sql

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

Output Code:

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE sampleMacro ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
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);
        --** SSC-FDM-0020 - 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]