MSCEWI4038

Snowflake Scripting cursor rows are not modifiable.

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

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

PreviousMSCEWI4037 NextMSCEWI4039

Last updated 1 year ago

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;

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