Snowflake reopen a cursor with different parameters

Question

I'm trying to reuse a cursor in my Snowflake procedure but I get the error message that the cursor is already open.

CREATE OR REPLACE PROCEDURE SP_PAT_LIST()

returns varchar null

LANGUAGE SQL

STRICT

EXECUTE AS CALLER
AS 
$$
  declare

    cPat CURSOR for select PAT_ID from values(1),(2),(3),(4) x (PAT_ID) 
                    where rlike(PAT_ID,?,'i');

    sPatId varchar;

    sPatIdList varchar default '';

    sDelimiter default '';

  begin

    begin

      open cPat using ('[2,4]');

      for cRow in cPat do

        sPatIdList := sPatIdList||sDelimiter||cRow.Pat_Id;

        sDelimiter := ',';


      end for;

      close cPat;

    end;

    begin

      open cPat using ('[1,3]');

      for cRow in cPat do

        sPatIdList := sPatIdList||sDelimiter||cRow.Pat_Id;

        sDelimiter := ',';

      end for;

      close cPat;

    end;

    return sPatIdList;

  end;

$$;

user_9879379 · Answer

I can confirm that this is a known issue which has been fixed, but not released yet.

For now, please create a different cursor to workaround the issue.

It should be released soon, but I don't know the exact date.

user_5070879 · Answer

A workaround could be defining the cursor using let twice. They are inside two different(not overlapping) begin/end blocks so it will work:

declare
    sPatId varchar;
    sPatIdList varchar default '';
    sDelimiter default '';
begin
   begin
     let cPat CURSOR for select PAT_ID from values(1),(2),(3),(4) x (PAT_ID) 
                         where rlike(PAT_ID,?,'i');

      open cPat using ('[2,4]');

     for cRow in cPat do
       sPatIdList := sPatIdList||sDelimiter||cRow.Pat_Id;
       sDelimiter := ',';
     end for;
      close cPat;
    end;


    begin
      let cPat CURSOR for select PAT_ID from values(1),(2),(3),(4) x (PAT_ID) 
                          where rlike(PAT_ID,?,'i');

      open cPat using ('[1,3]');
      for cRow in cPat do
        sPatIdList := sPatIdList||sDelimiter||cRow.Pat_Id;
        sDelimiter := ',';
      end for;
      close cPat;
    end;

return sPatIdList;
end;

Output:

Snowflake reopen a cursor with different parameters

2 Answers