'Snowflake reopen a cursor with different parameters
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;
$$;
Solution 1:[1]
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.
Solution 2:[2]
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:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Eric Lin |
| Solution 2 | Lukasz Szozda |