MSCEWI4039

Multiple SET Statements for the same cursor found.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

This EWI is added when multiple SET Statements for the same cursor are found; All additional SET Statements are also commented out. This happens because having multiple SET Statements for the same cursor is not valid in Snowflake Scripting.

Example Code:

Input Code:

CREATE OR ALTER PROCEDURE multipleSetExample
AS
BEGIN
    DECLARE @MyCursor CURSOR;
    DECLARE @MyCursor2 CURSOR STATIC READ_ONLY
	FOR  
	SELECT FirstName
	FROM vEmployee;
    DECLARE @MyCursor3 CURSOR;
    
    SET @MyCursor = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;
        
    SET @MyCursor3 = CURSOR STATIC READ_ONLY
    FOR
    SELECT *
    FROM someTable;
    
    SET @MyCursor = CURSOR DYNAMIC
        FOR
        SELECT col2
        FROM exampleTable;
        
    SET @MyCursor2 = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;
        
    RETURN 'DONE';
END;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.multipleSetExample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MYCURSOR CURSOR
        FOR
            SELECT
                col3
            FROM
                PUBLIC.defaultTable;
        MYCURSOR2 CURSOR
        FOR
            SELECT
                FirstName
            FROM
                PUBLIC.vEmployee;
        MYCURSOR3 CURSOR
        FOR
            SELECT
                *
            FROM
                PUBLIC.someTable;
    BEGIN
-- ** MSC-ERROR - MSCEWI4039 - CURSOR VARIABLE MYCURSOR SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING **
--        SET MYCURSOR = CURSOR DYNAMIC
--            FOR
--            SELECT
--            col2
--            FROM
--            PUBLIC.exampleTable;
-- ** MSC-ERROR - MSCEWI4039 - CURSOR VARIABLE MYCURSOR2 SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING **
--    SET MYCURSOR2 = CURSOR STATIC READ_ONLY
--        FOR
--        SELECT
--            col3
--        FROM
--            PUBLIC.defaultTable;
        RETURN 'DONE';
    END;
$$;

Recommendations

• If you need more support, you can email us at [e-mail]