SELECT

Translation reference for SELECT statement inside procedures in Transact-SQL.

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.

Applies to

• SQL Server

• Azure Synapse Analytics

Multiple result sets are returned in temporary tables

Description

Snowflake SQL support returning tables in as a return type for Stored Procedures, but unlike Transact-SQL, Snowflake does not support returning multiple resultsets in the same procedure. For this scenario, all the query IDs are stored in a temporary table and returned as an array.

Sample Source Patterns

The following example details the transformation when there is only one SELECT statement in the procedure.

Transact-SQL

Single Resultset

IN -> SqlServer_01.sql

CREATE PROCEDURE SOMEPROC()
AS
BEGIN
        SELECT * from AdventureWorks.HumanResources.Department;
END

Output

Snowflake SQL

Single Resultset

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
        DECLARE
                ProcedureResultSet RESULTSET;
        BEGIN
                ProcedureResultSet := (
                SELECT
                        *
                from
                        AdventureWorks.HumanResources.Department);
                RETURN TABLE(ProcedureResultSet);
        END;
$$;

Output

The following example details the transformation when there are many SELECT statements in the procedure.

Transact-SQL

Multiple Resultset

IN -> SqlServer_02.sql

CREATE PROCEDURE SOMEPROC()
AS
BEGIN
        SELECT * from AdventureWorks.HumanResources.Department;
        SELECT * from AdventureWorks.HumanResources.Shift;
END

Output

Snowflake SQL

Single Resultset

OUT -> SqlServer_02.sql

CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
        DECLARE
                ProcedureResultSet1 VARCHAR;
                ProcedureResultSet2 VARCHAR;
                return_arr ARRAY := array_construct();
        BEGIN
                ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
                CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
                        SELECT
                                *
                        from
                                AdventureWorks.HumanResources.Department;
                return_arr := array_append(return_arr, :ProcedureResultSet1);
                ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
                CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
                        SELECT
                                *
                        from
                                AdventureWorks.HumanResources.Shift;
                return_arr := array_append(return_arr, :ProcedureResultSet2);
                --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
                RETURN return_arr;
        END;
$$;

Output

Known Issues

1. The query results should be accessed by using the IDs returned by the Stored Procedure

Related EWIs

1. SSC-FDM-0020: Multiple result sets are returned in temporary tables.