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.

IN -> Teradata_01.sql
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');

Basic Cursor

Teradata

IN -> Teradata_02.sql
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;

Snowflake Scripting

OUT -> Teradata_02.sql
CREATE OR REPLACE PROCEDURE CursorsTest ()