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

IN -> SqlServer_01.sql
CREATE PROCEDURE SOMEPROC()
AS
BEGIN
        SELECT * from AdventureWorks.HumanResources.Department;
END

Snowflake SQL

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;
$$;

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

SQL Server

IN -> SqlServer_02.sql
CREATE PROCEDURE SOMEPROC()
AS
BEGIN
        SELECT * from AdventureWorks.HumanResources.Department;
        SELECT * from AdventureWorks.HumanResources.Shift;
END

Snowflake SQL

OUT -> SqlServer_02.sql
CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS ARRAY