Links

MSCEWI1081

Snowflake Scripting procedures cannot return more that one result set

Severity

Medium

Description

This EWI is added when the source code is intended to return more than one Result Set from a single procedure. It is also added when there are Output Parameters and Dynamic Result Sets together in the same procedure. Snowflake Scripting procedures only allow one thing to be returned per procedure.

Example Code

Input Code:

The following procedure is intended to return two Result Sets, since it has the DYNAMIC RESULT SETS 2 property in the header and two cursors are opened in the body.
CREATE PROCEDURE sampleProcedure ()
DYNAMIC RESULT SETS 2
BEGIN
DECLARE result_set CURSOR WITH RETURN ONLY FOR
SELECT *
FROM SampleTable1, SampleTable2;
DECLARE result_set2 CURSOR WITH RETURN ONLY FOR
SELECT Column11
FROM SampleTable1;
OPEN result_set2;
OPEN result_set;
END;

Output Code:

As expected behavior, the cursor to be returned is the first one opened. The EWI will be added warning that may be data lost since the second cursor opened will not be returned.
CREATE OR REPLACE PROCEDURE spZero ()
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
LET result_set CURSOR FOR
SELECT *
FROM SampleTable1, SampleTable2;
OPEN result_set;
/*** MSC-ERROR - MSCEWI1081 - SNOWFLAKE SCRIPTING PROCEDURES CANNOT RETURN MORE THAT ONE RESULT SET ***/
RETURN TABLE(resultset_from_cursor(result_set2));
END;
$$;

Recommendations