SELECT

Translation reference for SELECT statement inside procedures in SQL Server

Multiple result sets are returned in temporary tables

Description

Snowflake SQL support returning tables in as a return type for Stored Procedures, but unlike SQL Server, 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.

SQL Server

Single Resultset

IN -> SqlServer_01.sql

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

Output

DepartmentID	Name	GroupName
1	Engineering	Research and Development
2	Tool Design	Research and Development
3	Sales	Sales and Marketing
4	Marketing	Sales and Marketing
5	Purchasing	Inventory Management
6	Research and Development	Research and Development
7	Production	Manufacturing
8	Production Control	Manufacturing
9	Human Resources	Executive General and Administration
10	Finance	Executive General and Administration
11	Information Services	Executive General and Administration
12	Document Control	Quality Assurance
13	Quality Assurance	Quality Assurance
14	Facilities and Maintenance	Executive General and Administration
15	Shipping and Receiving	Inventory Management
16	Executive	Executive General and Administration

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

DepartmentID	Name	GroupName
1	Engineering	Research and Development
2	Tool Design	Research and Development
3	Sales	Sales and Marketing
4	Marketing	Sales and Marketing
5	Purchasing	Inventory Management
6	Research and Development	Research and Development
7	Production	Manufacturing
8	Production Control	Manufacturing
9	Human Resources	Executive General and Administration
10	Finance	Executive General and Administration
11	Information Services	Executive General and Administration
12	Document Control	Quality Assurance
13	Quality Assurance	Quality Assurance
14	Facilities and Maintenance	Executive General and Administration
15	Shipping and Receiving	Inventory Management
16	Executive	Executive General and Administration

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

SQL Server

Multiple Resultset

IN -> SqlServer_02.sql

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

Output

DepartmentID	Name	GroupName
1	Engineering	Research and Development
2	Tool Design	Research and Development
3	Sales	Sales and Marketing
4	Marketing	Sales and Marketing
5	Purchasing	Inventory Management
6	Research and Development	Research and Development
7	Production	Manufacturing
8	Production Control	Manufacturing
9	Human Resources	Executive General and Administration
10	Finance	Executive General and Administration
11	Information Services	Executive General and Administration
12	Document Control	Quality Assurance
13	Quality Assurance	Quality Assurance
14	Facilities and Maintenance	Executive General and Administration
15	Shipping and Receiving	Inventory Management
16	Executive	Executive General and Administration

ShiftID	Name	StartTime	EndTime	ModifiedDate
1	Day	07:00:00	15:00:00	2008-04-30 00:00:00.000
2	Evening	15:00:00	23:00:00	2008-04-30 00:00:00.000
3	Night	23:00:00	07:00:00	2008-04-30 00:00:00.000

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

DepartmentID	Name	GroupName
1	Engineering	Research and Development
2	Tool Design	Research and Development
3	Sales	Sales and Marketing
4	Marketing	Sales and Marketing
5	Purchasing	Inventory Management
6	Research and Development	Research and Development
7	Production	Manufacturing
8	Production Control	Manufacturing
9	Human Resources	Executive General and Administration
10	Finance	Executive General and Administration
11	Information Services	Executive General and Administration
12	Document Control	Quality Assurance
13	Quality Assurance	Quality Assurance
14	Facilities and Maintenance	Executive General and Administration
15	Shipping and Receiving	Inventory Management
16	Executive	Executive General and Administration

ShiftID	Name	StartTime	EndTime	ModifiedDate
1	Day	07:00:00	15:00:00	2008-04-30 00:00:00.000
2	Evening	15:00:00	23:00:00	2008-04-30 00:00:00.000
3	Night	23:00:00	07:00:00	2008-04-30 00:00:00.000

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.