SSC-FDM-0020

Multiple result sets are returned in temporary tables

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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]