MSCEWI3133

Cursor variable has already been assigned

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

Severity

Medium

Description

When an OPEN FOR statement is converted, a cursor assignment with the same name as the cursor variable used in the input code is added along with other statements to emulate its functionality. Since it is possible to use multiple OPEN FOR statements with the same cursor variable, there will be multiple cursor assignments with the same name in the output code. Leaving the output code as it is will cause compilation errors when executed in Snowflake.

Example code

Input code

CREATE OR REPLACE PROCEDURE open_for_procedure
AS 
	query1 VARCHAR(200) := 'SELECT 123 FROM dual';
	query2 VARCHAR(200) := 'SELECT 456 FROM dual';
	my_cursor_variable SYS_REFCURSOR;
BEGIN
	OPEN my_cursor_variable FOR query1;
	OPEN my_cursor_variable FOR query2;
END;

Output code

CREATE OR REPLACE PROCEDURE MYDB.PUBLIC.open_for_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	DECLARE
		query1 VARCHAR(200) := 'SELECT 123 FROM dual';
		query2 VARCHAR(200) := 'SELECT 456 FROM dual';
		my_cursor_variable_res RESULTSET;
	BEGIN
		my_cursor_variable_res := (
			EXECUTE IMMEDIATE :query1
		);
		LET my_cursor_variable CURSOR
		FOR
			my_cursor_variable_res;
		OPEN my_cursor_variable;
		my_cursor_variable_res := (
			EXECUTE IMMEDIATE :query2
		);
		--** MSC-ERROR - MSCEWI3133 - THE CURSOR VARIABLE NAMED 'my_cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR **
		LET my_cursor_variable CURSOR
		FOR
			my_cursor_variable_res;
		OPEN my_cursor_variable;
	END;
$$;

Recommendations

• To solve the compilation errors of the output code the cursor assignments that have the MSCEWI3133 message should be renamed.

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