📘 Translation ReferencesSQLServer Snowflake Scripting 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
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 ;
$$;
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
Known Issues
The query results should be accessed by using the IDs returned by the Stored Procedure
Related EWIs
SSC-FDM-0020 : Multiple result sets are returned in temporary tables.
Last updated 7 months ago