CURSOR

Translation reference to convert SQL Server CUROR statement to Snowflake Scripting

Some parts in the output code are omitted for clarity reasons.

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.

IN -> SqlServer_01.sql

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

Snowflake Scripting

OUT -> SqlServer_01.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":"transact"}}'
;

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 TABLE()