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;
$$;
