SSC-EWI-TS0039

Multiple SET Statements for the same cursor found.

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:

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:

IN -> SqlServer_01.sql

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:

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE multipleSetExample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		MYCURSOR CURSOR
		FOR
			SELECT col3
			FROM defaultTable;
		MYCURSOR2 CURSOR
		FOR
			SELECT
				FirstName
			FROM
				vEmployee;
		MYCURSOR3 CURSOR
		FOR
			SELECT *
			FROM someTable;
	BEGIN
		 
		 
		 
		 
		 
		!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0039 - CURSOR VARIABLE MYCURSOR SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING ***/!!!

		SET @MyCursor = CURSOR DYNAMIC
		    FOR
		    SELECT col2
		    FROM exampleTable;
		!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0039 - CURSOR VARIABLE MYCURSOR2 SET MULTIPLE TIMES, THIS IS NOT VALID IN SNOWFLAKE SCRIPTING ***/!!!

    SET @MyCursor2 = CURSOR STATIC READ_ONLY
        FOR
        SELECT col3
        FROM defaultTable;
		RETURN 'DONE';
	END;
$$;

Recommendations

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