This is a deprecated version of the SnowConvert documentation, please visit the official site .
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 .
