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 Output
Copy CREATE PROCEDURE SOMEPROC()
AS
BEGIN
SELECT * from AdventureWorks.HumanResources.Department;
END
DepartmentID Name GroupName Executive General and Administration
Executive General and Administration
Executive General and Administration
Facilities and Maintenance
Executive General and Administration
Executive General and Administration
Snowflake SQL
Single Resultset Output
Copy 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 ;
$$;
DepartmentID Name GroupName Executive General and Administration
Executive General and Administration
Executive General and Administration
Facilities and Maintenance
Executive General and Administration
Executive General and Administration
The following example details the transformation when there are many SELECT statements in the procedure.
SQL Server
Multiple Resultset Output
Copy CREATE PROCEDURE SOMEPROC()
AS
BEGIN
SELECT * from AdventureWorks.HumanResources.Department;
SELECT * from AdventureWorks.HumanResources.Shift;
END
DepartmentID Name GroupName Executive General and Administration
Executive General and Administration
Executive General and Administration
Facilities and Maintenance
Executive General and Administration
Executive General and Administration
ShiftID Name StartTime EndTime ModifiedDate
Snowflake SQL
Single Resultset Output
Copy CREATE OR REPLACE PROCEDURE SOMEPROC ()
RETURNS ARRAY