CURSOR

Translation reference to convert SQL Server CUROR statement to Snowflake Scripting

Description

Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type. For more information check here.

//ISO Syntax  
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR   
     FOR select_statement   
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]  
[;]  

//Transact-SQL Extended Syntax  
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]   
     [ FORWARD_ONLY | SCROLL ]   
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
     [ TYPE_WARNING ]   
     FOR select_statement   
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]  
[;]  

FETCH   
          [ [ NEXT | PRIOR | FIRST | LAST   
                    | ABSOLUTE { n | @nvar }   
                    | RELATIVE { n | @nvar }   
               ]   
               FROM   
          ]   
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }   
[ INTO @variable_name [ ,...n ] ] 

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }  

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }  

Sample Source Patterns

SQL Server

Notice that the following parameters are inherently supported by Snowflake Scripting.

• [LOCAL].

• [FORWARD_ONLY].

• [FAST_FORWARD] Specifies a FORWARD_ONLY (FETCH NEXT only) and READ_ONLY

• [READ_ONLY] the WHERE CURRENT OF does not exist in Snowflake Scripting.

Cursor

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

INSERT INTO vEmployee(PersonID, LastName, FirstName) 
VALUES
    (1, 'AA', 'A'),
    (2, 'BB', 'B'),
    (3, 'CC', 'C'),
    (4, 'DD', 'D'),
    (5, 'EE', 'E'),
    (6, 'FF', 'F'),
    (7, 'GG', 'G');
	
CREATE OR ALTER PROCEDURE CursorExample
AS
    DECLARE 
        @CursorVar CURSOR, 
	@firstName VARCHAR;

    SET @CursorVar = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
	FOR  
	SELECT FirstName
	FROM vEmployee;  

    OPEN @CursorVar;

    FETCH NEXT FROM @CursorVar INTO @firstName;
    FETCH NEXT FROM @CursorVar INTO @firstName;

    CLOSE @CursorVar;

    SELECT @firstName;
GO

Result

B

Snowflake Scripting

Cursor

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

INSERT INTO vEmployee(PersonID, LastName, FirstName) 
VALUES 
    (1,'AA', 'A'),
    (2,'BB', 'B'),
    (3,'CC', 'C'),
    (4,'DD', 'D'),
    (5,'EE', 'E'),
    (6,'FF', 'F'),
    (7,'GG', 'G');
    
CREATE OR REPLACE PROCEDURE CursorExample()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    
DECLARE  
    
    CURSORVAR CURSOR(p) FOR 
        SELECT FirstName FROM vEmployee;  
    firstName VARCHAR(255);
    
BEGIN
    OPEN CURSORVAR;
    FETCH CURSORVAR INTO firstName;
    FETCH CURSORVAR INTO firstName;
    CLOSE CURSORVAR;   
    
    RETURN firstName;    

END;    
$$;

CALL CursorExample();

Result

B

Known Issues

The following parameters are not supported:

DECLARE CURSOR

• [ GLOBAL ] Allows referencing the cursor name in any stored procedure or batch executed by the connection. Snowflake Scripting only allows the use of the cursor locally.

• [ SCROLL ] Snowflake Scripting only support FETCH NEXT.

• [ KEYSET | DYNAMIC ] If after opening a cursor and update to the table is made, these options may display some of the changes when fetching the cursor, Snowflake scripting only supports STATIC, in other words, after the cursor is opened the changes to the table are not detected by the cursor.

• [SCROLL_LOCKS] Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed, Snowflake Scripting cannot guarantee it.

• [OPTIMISTIC] When an update or delete is made through the cursor it uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. Snowflake Scripting does not have an internal process to replicate it.

• [TYPE_WARNING]

FETCH

• [PRIOR | FIRST | LAST] Snowscripting only support NEXT.

• [ABSOLUTE] Snowflake Scripting only supports NEXT but the behavior can be replicated.

• [RELATIVE] Snowflake Scripting but the behavior can be replicated.

• [ GLOBAL ] Allows referencing the cursor name in any stored procedure or batch executed by the connection. Snowflake Scripting only allows the use of the cursor locally.

• FETCH without INTO is not supported.

• When the FETCH statement is located inside a loop it is considered a complex pattern as it may have an impact on the Snowflake translated code performance. Check the related issues section for more information.

Fetch inside loop sample

Input

CREATE OR ALTER PROCEDURE cursor_procedure1
AS
BEGIN
DECLARE cursor1 CURSOR FOR SELECT col1 FROM my_table;
WHILE 1=0
   BEGIN
      FETCH NEXT FROM @cursor1 INTO @variable1;
   END
END;

Output

CREATE OR REPLACE PROCEDURE cursor_procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      /*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
      cursor1 CURSOR
      FOR
         SELECT
            col1
         FROM
            my_table;
   BEGIN
       
      WHILE (1=0) LOOP
         --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
         FETCH
            CURSOR1
            INTO
            :VARIABLE1;
      END LOOP;
   END;
$$;

OPEN

• [ GLOBAL ] Allows referencing the cursor name in any stored procedure or batch executed by the connection. Snowflake Scripting only allows the use of the cursor locally.

CLOSE

• [ GLOBAL ] Allows referencing the cursor name in any stored procedure or batch executed by the connection. Snowflake Scripting only allows the use of the cursor locally.

DEALLOCATED Removes a cursor reference and there is no equivalent in Snowflake Scripting.

WHERE CURRENT OF the use of this statement is not supported, for example:

CREATE OR ALTER PROCEDURE CursorWithCurrent
AS
    DECLARE 
        @CursorVar CURSOR;

    SET @CursorVar = CURSOR 
	FOR  
	SELECT FirstName  
	FROM vEmployee;  

    OPEN @CursorVar;

    FETCH NEXT FROM @CursorVar;
    FETCH NEXT FROM @CursorVar;

    UPDATE vEmployee SET LastName = 'Changed' WHERE CURRENT OF @CursorVar;

    CLOSE @CursorVar;
GO

Environment variables

• @@CURSOR_ROWS

• @@FETCH_STATUS

Related EWIs

1. MSCEWI4038: Snowflake Scripting cursor rows are not modifiable.

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