Links

MSCEWI4038

Snowflake Scripting cursor rows are not modifiable.

Severity

Medium

Description

This EWI is added when Cursors are open to modification in the input code. Snowflake Scripting does not allow modifying cursor rows.

Example Code:

Input Code:

CREATE OR ALTER PROCEDURE modifiablecursorTest
AS
BEGIN
-- Should be marked with MSCEWI4038
DECLARE CursorVar CURSOR
FOR
SELECT FirstName
FROM vEmployee;
DECLARE CursorVar2 INSENSITIVE CURSOR
FOR
SELECT FirstName
FROM vEmployee;
DECLARE CursorVar3 CURSOR KEYSET SCROLL_LOCKS
FOR
SELECT FirstName
FROM vEmployee;
DECLARE CursorVar4 CURSOR DYNAMIC OPTIMISTIC
FOR
SELECT FirstName
FROM vEmployee;
DECLARE CursorVar6 CURSOR STATIC
FOR
SELECT FirstName
FROM vEmployee;
DECLARE CursorVar7 CURSOR READ_ONLY
FOR
SELECT FirstName
FROM vEmployee;
-- Shouid not be marked
DECLARE CursorVar5 CURSOR STATIC READ_ONLY
FOR
SELECT FirstName
FROM vEmployee;
RETURN 'DONE';
END;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.modifiablecursorTest ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
-- Should be marked with MSCEWI4038
/*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
CursorVar CURSOR
FOR
SELECT
FirstName
FROM
PUBLIC.vEmployee;
/*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
CursorVar2 CURSOR
FOR
SELECT
FirstName
FROM
PUBLIC.vEmployee;
/*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
CursorVar3 CURSOR
FOR
SELECT
FirstName
FROM
PUBLIC.vEmployee;
/*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
CursorVar4 CURSOR
FOR
SELECT
FirstName
FROM
PUBLIC.vEmployee;
/*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
CursorVar6 CURSOR
FOR
SELECT
FirstName
FROM
PUBLIC.vEmployee;
/*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
CursorVar7 CURSOR
FOR
SELECT
FirstName
FROM
PUBLIC.vEmployee;
-- Shouid not be marked
CursorVar5 CURSOR
FOR
SELECT
FirstName
FROM
PUBLIC.vEmployee;
BEGIN
RETURN 'DONE';
END;
$$;

Recommendations