SELECT

Translation reference for SELECT statement inside procedures in SQL Server

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

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

Output

Snowflake SQL

Single Resultset

CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS TABLE()
LANGUAGE SQL
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.

SQL Server

Multiple Resultset

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

Output

Snowflake SQL

Single Resultset

CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS ARRAY
LANGUAGE SQL
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.Employee;
                return_arr := array_append(return_arr, :ProcedureResultSet2);
                --** MSC-WARNING - MSCEWI1100 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
                RETURN return_arr;
        END;
$$;

//The result of each query should be retrieved by using its ID
SELECT * FROM "RESULTSET_8BBEBCEB_2EDB_4272_BB4A_EFB85A09C85A";
SELECT * FROM "RESULTSET_56F156C9_5A56_49C4_BB7E_AF8AF685F365";

Output

Known Issues

The queries results should be accessed by using the IDs returned by the Stored Procedure

Related EWIs

1. MSCEWI1100: MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES