MSCEWI1081

Snowflake Scripting procedures cannot return more that one result set

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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

• If you need more support, you can email us at [email protected]