---
description: >-
  Translation reference to convert Teradata CURSOR statement to Snowflake
  Scripting
---

# CURSOR

{% hint style="info" %}
**Important Notice: Migration of Documentation Website**

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

[Official Snowflake Snowconvert Documentation](https://docs.snowflake.com/en/migrations/snowconvert-docs/translation-references/teradata/README)

For any immediate assistance or if you encounter any issues, please contact our support team at [snowconvert-support@snowflake.com](mailto:snowconvert-support@snowflake.com).

Thank you for your understanding.
{% endhint %}

## Description <a href="#description" id="description"></a>

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](https://docs.teradata.com/r/Teradata-VantageTM-SQL-Stored-Procedures-and-Embedded-SQL/March-2019/SQL-Cursor-Control-and-DML-Statements).

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

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

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

```sql
CLOSE cursor_name ;
```

## Sample Source Patterns <a href="#sample-source-patterns" id="sample-source-patterns"></a>

### Setup Data

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

{% tabs %}
{% tab title="Teradata" %}
{% code title="IN -> Teradata_01.sql" %}
```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');
```
{% endcode %}
{% endtab %}

{% tab title="Snowflake" %}
{% code title="OUT -> Teradata_01.sql" overflow="wrap" %}
```sql
CREATE OR REPLACE TABLE vEmployee (
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE TABLE ResTable (
    Column1 VARCHAR(255)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

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 OR REPLACE TABLE TEST_TABLE (
    ColumnA NUMBER(38, 18),
    ColumnB VARCHAR(8),
    ColumnC VARCHAR(8))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    * FROM
    TEST_TABLE;

    INSERT INTO TEST_TABLE
    VALUES (1, '1', '1');

    INSERT INTO TEST_TABLE
    VALUES (2, '2', '2');
```
{% endcode %}
{% endtab %}
{% endtabs %}

### Basic Cursor

#### Teradata

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="IN -> Teradata_02.sql" %}
```plsql
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;
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
Johnson
```
{% endtab %}
{% endtabs %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="OUT -> Teradata_02.sql" %}
```plsql
CREATE OR REPLACE PROCEDURE CursorsTest ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        val1 VARCHAR(255);
    BEGIN
         
        LET 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;
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
Johnson
```
{% endtab %}
{% endtabs %}

### 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

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="IN -> Teradata_03.sql" %}
```sql
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();
```
{% endcode %}
{% endtab %}

{% tab title="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     |
```
{% endtab %}
{% endtabs %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="OUT -> Teradata_03.sql" %}
```plsql
CREATE OR REPLACE PROCEDURE spSimple ()
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
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();
```
{% endcode %}
{% endtab %}

{% tab title="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   |
```
{% endtab %}
{% endtabs %}

### 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

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="IN -> Teradata_04.sql" %}
```sql
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();
```
{% endcode %}
{% endtab %}

{% tab title="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     |
```
{% endtab %}
{% endtabs %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="OUT -> Teradata_04.sql" overflow="wrap" %}
```sql
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "SampleTable2", "SampleTable1" **
CREATE OR REPLACE PROCEDURE spTwoOrMore ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
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
				SampleTable2;
		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
				Column11 FROM
				SampleTable1;
		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);
		--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
		RETURN return_arr;
	END;
$$;

CALL spTwoOrMore();

```
{% endcode %}
{% endtab %}

{% tab title="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     |

```
{% endtab %}
{% endtabs %}

### Cursors With Binding Variables

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

#### Teradata

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="IN -> Teradata_05.sql" %}
```sql
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;
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |
```
{% endtab %}
{% endtabs %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="OUT -> Teradata_05.sql" %}
```sql
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TEST_TABLE" **
CREATE OR REPLACE PROCEDURE TestProcedure (PARAM1 NUMBER(38, 18), PARAM2 VARCHAR(8), PARAM3 VARCHAR(8))
RETURNS TABLE (
)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET cursorExample CURSOR
      FOR
         SELECT
            * FROM
            TEST_TABLE
           	WHERE ColumnA = ?
            AND ColumnB ILIKE ?
            and ColumnC ILIKE ?;
      OPEN cursorExample USING (param1, param2, param3);
      RETURN TABLE(resultset_from_cursor(cursorExample));
   END;
$$;
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
|ColumnA|ColumnB|ColumnC|
+-------+-------+-------+
|      2|2      |2      |
```
{% endtab %}
{% endtabs %}

### 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

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="IN -> Teradata_06.sql" %}
```sql
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;
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
|Column1|
+-------+
|      1|
|      2|
```
{% endtab %}
{% endtabs %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="OUT -> Teradata_06.sql" %}
```sql
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "TEST_TABLE", "ResTable" **
CREATE OR REPLACE PROCEDURE TestProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        !!!RESOLVE EWI!!! /*** SSC-EWI-0110 - TRANSFORMATION NOT PERFORMED DUE TO MISSING DEPENDENCIES ***/!!!
        temp_fUsgClass_columnA;
    BEGIN
        LET cUsgClass CURSOR
        FOR
            SELECT
                columnA FROM
                TEST_TABLE;
        --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
        FOR fUsgClass IN cUsgClass DO
            temp_fUsgClass_columnA := fUsgClass.columnA;
            INSERT INTO ResTable (Column1)
            VALUES (:temp_fUsgClass_columnA);
        END FOR;
    END;
$$;

CALL TestProcedure();

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "ResTable" **
SELECT
    * FROM
    ResTable;

```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
|Column1|
+-------+
|      1|
|      2|
```
{% endtab %}
{% endtabs %}

### 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

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="IN -> Teradata_07.sql" %}
```sql
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;
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
|Column1|
+-------+
|      2|
```
{% endtab %}
{% endtabs %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Cursor Code" %}
{% code title="OUT -> Teradata_07.sql" %}
```sql
CREATE OR REPLACE PROCEDURE teradata_fetch_inside_loop ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/18/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        col_name VARCHAR(255);
        col_int INTEGER DEFAULT 1;
    BEGIN
         
         
        LET cursor_var CURSOR
        FOR
            SELECT
                columnA FROM
                TEST_TABLE;
                WHILE (:col_int <> 0) LOOP
            --** SSC-PRF-0003 - 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;
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
|Column1|
+-------+
|      2|
```
{% endtab %}
{% endtabs %}

## 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. [SSC-FDM-0020](../../../general/technical-documentation/issues-and-troubleshooting/functional-difference/general/ssc-fdm-0020): Multiple result sets are returned in temporary tables.
2. [SSC-PRF-0003](../../../general/technical-documentation/issues-and-troubleshooting/performance-review/general/ssc-prf-0003): Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance.
3. [SSC-PRF-0004](../../../general/technical-documentation/issues-and-troubleshooting/performance-review/general/ssc-prf-0004): This statement has usages of cursor for loop.
