MSCEWI3110

For Update Clause is not supported in Snowflake

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

Severity

Low

Description

There is no equivalent for FOR UPDATE clause in Snow Scripting so an EWI is added and the clause is commented out

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE for_update_clause
AS
    update_record f_employee%rowtype;
    CURSOR c1 IS SELECT * FROM f_employee FOR UPDATE OF employee_number nowait;
BEGIN
    FOR CREC IN C1 LOOP
	UPDATE f_employee SET employee_number = employee_number + 1000 WHERE CURRENT OF c1;
	IF crec.id = 2 THEN
	    DELETE FROM f_employee WHERE CURRENT OF c1;
	    EXIT;
	END IF;
    END LOOP;
END;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.for_update_clause ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      update_record VARIANT;
      c1 CURSOR FOR SELECT * FROM PUBLIC.f_employee
-- ** MSC-WARNING - MSCEWI3110 - FOR UPDATE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
--                                                    FOR UPDATE OF employee_number nowait
                                                                                        ;
   BEGIN
      FOR CREC IN C1 DO
-- ** MSC-ERROR - MSCEWI1075 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
--         UPDATE f_employee SET employee_number = /*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/
-- employee_number + 1000 WHERE CURRENT OF c1
                                           ;
         IF ( crec.id = 2) THEN
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'CURRENT OF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--            DELETE FROM PUBLIC.f_employee WHERE CURRENT OF c1
                                                              ;
            EXIT;
         END IF;
      END FOR;
   END;
$$;

Recommendations

• Handle the column update in the UPDATE/DELETE query for more details check MSCEWI1075.

• If you need more support, you can email us at [email protected]