CURSOR

Translation reference to convert Teradata CURSOR statement to Snowflake Scripting

Description

A cursor is a data structure that is used by stored procedures at runtime to point to a resultset returned by an SQL query. For more information check here.

DECLARE cursor_name [ SCROLL | NO SCROLL ] CURSOR
     [ 
          WITHOUT RETURN
          |
          WITH RETURN [ ONLY ] [ TO [ CALLER | CLIENT ] ]
     ]
     FOR
     cursor_specification [ FOR [ READ ONLY | UPDATE ] ]
     |
     statement_name
;

FETCH [ [ NEXT | FIRST ] FROM ] cursor_name INTO
    [ variable_name | parameter_name ] [ ,...n ]
;

OPEN cursor_name
    [ USING [ SQL_identifier | SQL_paramenter ] [ ,...n ] ]
;

CLOSE cursor_name ;

Sample Source Patterns

Setup Data

The following code is necessary to execute the sample patterns present in this section.

Teradata

CREATE TABLE vEmployee(
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255)
);

CREATE TABLE ResTable(
    Column1 VARCHAR(255)
);

INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (1, 'Smith', 'Christian');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (2, 'Johnson', 'Jhon');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (3, 'Brown', 'William');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (4, 'Williams', 'Gracey');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (5, 'Garcia', 'Julia');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (6, 'Miller', 'Peter');
INSERT INTO vEmployee(PersonID, LastName, FirstName) VALUES (7, 'Davis', 'Jannys');

CREATE TABLE TEST_TABLE (
    ColumnA NUMBER, 
    ColumnB VARCHAR(8),
    ColumnC VARCHAR(8));


SELECT * FROM TEST_TABLE;
INSERT INTO TEST_TABLE VALUES (1, '1', '1');
INSERT INTO TEST_TABLE VALUES (2, '2', '2');

Snowflake

CREATE TABLE PUBLIC.vEmployee(
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255)
);

CREATE TABLE PUBLIC.ResTable(
    Column1 VARCHAR(255)
);

INSERT INTO PUBLIC.vEmployee (PersonID, LastName, FirstName) VALUES (1, 'Smith', 'Christian');
INSERT INTO PUBLIC.vEmployee (PersonID, LastName, FirstName) VALUES (2, 'Johnson', 'Jhon');
INSERT INTO PUBLIC.vEmployee (PersonID, LastName, FirstName) VALUES (3, 'Brown', 'William');
INSERT INTO PUBLIC.vEmployee (PersonID, LastName, FirstName) VALUES (4, 'Williams', 'Gracey');
INSERT INTO PUBLIC.vEmployee (PersonID, LastName, FirstName) VALUES (5, 'Garcia', 'Julia');
INSERT INTO PUBLIC.vEmployee (PersonID, LastName, FirstName) VALUES (6, 'Miller', 'Peter');
INSERT INTO PUBLIC.vEmployee (PersonID, LastName, FirstName) VALUES (7, 'Davis', 'Jannys');

CREATE TABLE PUBLIC.TEST_TABLE (
	ColumnA NUMBER(38, 18),
	ColumnB VARCHAR(8),
	ColumnC VARCHAR(8));

INTO PUBLIC.TEST_TABLE VALUES (1, '1', '1');
INSERT INTO PUBLIC.TEST_TABLE VALUES (2, '2', '2');

Basic Cursor

Teradata

Cursor Code

REPLACE PROCEDURE CursorsTest()
BEGIN
    DECLARE val1 VARCHAR(255);
    DECLARE empcursor CURSOR FOR
        SELECT LastName
        FROM vEmployee
        ORDER BY PersonID;
    
    OPEN empcursor;
    FETCH NEXT FROM empcursor INTO val1;
    FETCH NEXT FROM empcursor INTO val1;
    INSERT INTO ResTable(Column1) VALUES (val1);
    CLOSE empcursor;
END;

CALL CursorsTest();
SELECT * FROM ResTable;

Result

Johnson

Snowflake Scripting

Cursor Code

CREATE OR REPLACE PROCEDURE PUBLIC.CursorsTest ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET val1 VARCHAR(255);
      LET empcursor CURSOR FOR SELECT
      LastName
      FROM PUBLIC.vEmployee ORDER BY PersonID;

      OPEN empcursor;

      FETCH NEXT FROM empcursor INTO val1;
      FETCH NEXT FROM empcursor INTO val1;
      INSERT INTO PUBLIC.ResTable (Column1) VALUES (:val1);
      CLOSE empcursor;
   END;
$$;

CALL CursorsTest();
SELECT * FROM PUBLIC.ResTable;

Result

Johnson

Single Returnable Cursor

The following procedure is intended to return one result set since it has the DYNAMIC RESULT SETS 1 property in the header, the cursor has the WITH RETURN property and is being opened in the body.

Teradata

Cursor Code

REPLACE PROCEDURE spSimple ()
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE result_set CURSOR WITH RETURN ONLY FOR
    SELECT *
    FROM vEmployee;
        
    OPEN result_set;
END;

CALL spSimple();

Result

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Snowflake Scripting

Cursor Code

CREATE OR REPLACE PROCEDURE spSimple ()
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET result_set CURSOR FOR SELECT
      *
      FROM vEmployee;

      OPEN result_set;
      RETURN TABLE(resultset_from_cursor(result_set));
   END;
$$;

CALL spSimple();

Result

PERSONID|LASTNAME|FIRSTNAME|
--------+--------+---------+
       1|Smith   |Christian|
       2|Johnson |Jhon     |
       3|Brown   |William  |
       4|Williams|Gracey   |
       5|Garcia  |Julia    |
       6|Miller  |Peter    |
       7|Davis   |Jannys   |

Multiple Returnable Cursors

The following procedure is intended to return multiple results when DYNAMIC RESULT SETS property in the header is greater than 1, the procedure has multiple cursors with the WITH RETURN property and these same cursors are being opened in the body.

Teradata

Cursor Code

REPLACE PROCEDURE spTwoOrMore()
DYNAMIC RESULT SETS 2
BEGIN
    DECLARE result_set CURSOR WITH RETURN ONLY FOR
        SELECT * FROM SampleTable2;

    DECLARE result_set2 CURSOR WITH RETURN ONLY FOR
	SELECT Column11 FROM SampleTable1;
    OPEN result_set2;
    OPEN result_set;
END;

CALL spTwoOrMore();

Result

ColumnA|ColumnB|ColumnC|
-------+-------+-------+
      2|2      |2      |
      1|1      |1      |

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Snowflake Scripting

Cursor Code

CREATE OR REPLACE PROCEDURE PUBLIC.spTwoOrMore ()
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    tbl_result_set VARCHAR;
    tbl_result_set2 VARCHAR;
    return_arr ARRAY := array_construct();
BEGIN
    tbl_result_set := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set) AS
        SELECT * FROM PUBLIC.vEmployee;
    LET result_set CURSOR FOR
	SELECT * FROM IDENTIFIER(?);
	
    tbl_result_set2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
    CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set2) AS
        SELECT * FROM PUBLIC.TEST_TABLE;
    LET result_set2 CURSOR FOR
	SELECT * FROM IDENTIFIER(?);
	
    OPEN result_set2 USING (tbl_result_set2);
    return_arr := array_append(return_arr, :tbl_result_set2);
    
    OPEN result_set USING (tbl_result_set);
    return_arr := array_append(return_arr, :tbl_result_set);
    
    /*** MSC-WARNING - MSCEWI1100 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES ***/
    RETURN return_arr;
END;
$$;

CALL spTwoOrMore();

SELECT * FROM "RESULTSET_B5B0005D_1602_48B7_9EE4_62E1A28B000C";
SELECT * FROM "RESULTSET_1371794D_7B77_4DA9_B42E_7981F35CEA9C";

Results

[
  "RESULTSET_B5B0005D_1602_48B7_9EE4_62E1A28B000C",
  "RESULTSET_1371794D_7B77_4DA9_B42E_7981F35CEA9C"
]

ColumnA|ColumnB|ColumnC|
-------+-------+-------+
      2|2      |2      |
      1|1      |1      |

PersonID|LastName|FirstName|
--------+--------+---------+
       7|Davis   |Jannys   |
       5|Garcia  |Julia    |
       3|Brown   |William  |
       1|Smith   |Christian|
       6|Miller  |Peter    |
       4|Williams|Gracey   |
       2|Johnson |Jhon     |

Cursos With Binding Variables

The following cursor uses binding variables as the were condition to perform the query.

Teradata

Cursor Code

REPLACE PROCEDURE TestProcedure (IN param1 NUMBER, param2 VARCHAR(8), param3 VARCHAR(8))
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE cursorExample CURSOR WITH RETURN ONLY FOR
        SELECT * FROM  TEST_TABLE
   	WHERE ColumnA = param1 AND ColumnB LIKE param2 and ColumnC LIKE param3;
    
    OPEN cursorExample;	  
END;

CALL TestProcedure(2, '2', '2');

Result

|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |

Snowflake Scripting

Cursor Code

CREATE OR REPLACE PROCEDURE PUBLIC.TestProcedure (PARAM1 FLOAT, PARAM2 STRING, PARAM3 STRING)
RETURNS TABLE(
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET cursorExample CURSOR
	FOR
	    SELECT
		* FROM
		PUBLIC.TEST_TABLE
		WHERE ColumnA = ?
		AND ColumnB LIKE ?
		and ColumnC LIKE ?;
	OPEN cursorExample USING (param1, param2, param3);
	RETURN TABLE(resultset_from_cursor(cursorExample));
    END;
$$;

CALL PUBLIC.TestProcedure(2, '2', '2');

Result

|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |

Cursor For Loop

It is a type of loop that uses a cursor to fetch rows from a SELECT statement and then performs some processing on each row.

Teradata

Cursor Code

REPLACE PROCEDURE TestProcedure ()
DYNAMIC RESULT SETS 1
BEGIN
    FOR fUsgClass AS cUsgClass CURSOR FOR
        SELECT columnA FROM  TEST_TABLE
    DO
        INSERT INTO ResTable(Column1) VALUES (fUsgClass.columnA);
    END FOR;
END;

CALL TestProcedure();
SELECT * FROM ResTable;

Result

|Column1|
+-------+
|      1|
|      2|

Snowflake Scripting

Cursor Code

CREATE OR REPLACE PROCEDURE TestProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET cUsgClass CURSOR
        FOR
            SELECT
                columnA FROM
                TEST_TABLE;
 
        FOR fUsgClass IN cUsgClass DO
            INSERT INTO ResTable (Column1)
            VALUES (:temp_fUsgClass_columnA);
        END FOR;
    END;
$$;

CALL PUBLIC.TestProcedure();
SELECT * FROM ResTable;

Result

|Column1|
+-------+
|      1|
|      2|

Cursor Fetch inside a Loop

It allows one to retrieve rows from a result set one at a time and perform some processing on each row.

Teradata

Cursor Code

REPLACE PROCEDURE teradata_fetch_inside_loop() 
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE col_name VARCHAR(255);
    DECLARE col_int INTEGER DEFAULT 1;
    DECLARE cursor_var CURSOR FOR SELECT columnA FROM TEST_TABLE;
    WHILE (col_int <> 0) DO		
        FETCH cursor_var INTO col_name;
        INSERT INTO ResTable(Column1) VALUES (cursor_var.columnA);
        SET col_int = 0;
    END WHILE;
END;

CALL teradata_fetch_inside_loop();
SELECT * FROM ResTable;

Result

|Column1|
+-------+
|      2|

Snowflake Scripting

Cursor Code

CREATE OR REPLACE PROCEDURE teradata_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET col_name VARCHAR(255);
        LET col_int INTEGER DEFAULT 1;
        LET cursor_var CURSOR
        FOR
            SELECT
                columnA FROM
                TEST_TABLE;
                WHILE (col_int <> 0) LOOP
                    --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
                    FETCH cursor_var INTO col_name;
                    INSERT INTO ResTable (Column1)
                    VALUES (cursor_var.columnA);
                    col_int := 0;
                END LOOP;
    END;
$$;

CALL teradata_fetch_inside_loop();
SELECT * FROM ResTable;

Result

|Column1|
+-------+
|      2|

Known Issues

The following parameters are not applicable in Snowflake Scripting.

1. Declare

[ SCROLL/NO SCROLL ] Snowflake Scripting only supports FETCH NEXT.

[ READ-ONLY ] This is the default in Snowflake Scripting.

[ UPDATE ].

2. Fetch

[ NEXT ] This is the default behavior in Snowflake Scripting.

[ FIRST ].

Related EWIs

1. MSCEWI1081: Snowflake Scripting procedures cannot return more than one result set

2. MSCCP0003: This statement has usages of cursor for loop

3. MSCCP0006: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance