Workaround for cursors using parameters or procedure variables

Description

This section describes how to simulate the usage of cursor parameters and procedure variables inside the query of a cursor. The name of the variables or parameters is replaced with bindings using the ? sign. Then, when the cursor is opened, the values should be passed with the USING clause.

Cursor with local variables

Use bindings for the query in the cursor for variable or procedure parameter used and open the cursor with the USING clause.

Oracle Cursor

CREATE OR REPLACE PROCEDURE oracle_cursor_sample
AS
    like_value VARCHAR(255); 
    CURSOR c1 IS SELECT region_name FROM hr.regions WHERE region_name LIKE like_value ORDER BY region_name;  
    r_name VARCHAR(255);
BEGIN
    like_value := 'E%';
    OPEN c1;
    FETCH c1 INTO r_name;
    CLOSE c1;
    like_value := 'A%';
    FOR r1 IN c1 LOOP
        NULL;
    END LOOP;
END;

Snowflake Scripting Cursor

CREATE OR REPLACE PROCEDURE oracle_cursor_sample()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    like_value VARCHAR(255);
    c1 CURSOR FOR SELECT region_name FROM hr.regions WHERE region_name LIKE ? ORDER BY region_name;
    r_name VARCHAR(255);
BEGIN
    like_value := 'E%';
    OPEN c1 USING (like_value);
    FETCH c1 INTO r_name;
    CLOSE c1;
    like_value := 'A%';
    OPEN c1 USING (like_value);
    FOR r1 IN c1 DO
        NULL;
    END FOR;
    CLOSE c1;
END;
$$;

Cursor with parameters

Use bindings for the query in the cursor for each parameter used and open the cursor with the USING clause. Keep in mind that a parameter that is used multiple times on a single cursor may require passing the variable multiple times in the USING clause.

Oracle Cursor

CREATE OR REPLACE PROCEDURE parametrized_cursor_sample AS
    CURSOR cursor1(param1 number) IS SELECT region_name FROM hr.regions where region_id = param1 ORDER BY region_name;
    var1 integer;
BEGIN
    OPEN cursor1(123);
    FETCH cursor1 INTO var1;
    CLOSE cursor1;
    FOR r1 IN cursor1(456) LOOP
        NULL;
    END LOOP;
END;

Snowflake Scripting Cursor

CREATE OR REPLACE PROCEDURE PUBLIC.parametrized_cursor_sample_fixed ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$
   DECLARE
      var1 STRING;
      cursor1 CURSOR FOR SELECT region_name FROM hr.regions where region_id = ? ORDER BY region_name;
   BEGIN
      NULL;
      OPEN cursor1 USING (1);
      FETCH cursor1 INTO var1;
      CLOSE cursor1;
      OPEN cursor1 USING (2);
      FOR r1 IN cursor1 DO
         NULL;
      END FOR;
      CLOSE cursor1;
   END;
$;