MSCEWI1038

Dynamic SQL statement may be unrecognized.

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

Severity

Low

Status: Deprecated

Description

This warning is added to let the user know about dynamically created statements that might not contain the correct Snowflake syntax. In the cases where a statement is created at runtime by concatenating strings literals, variables, columns, and other values, even though the final statement might have been valid in the source language platform, it might not be so in SnowFlake. This can happen for example if a string literal contained a SQL statement that was concatenated with others, the string literals will be converted as is (with no transformation rules), and the final statement might not work in Snowflake

Example Code

In the following example, it can be seen that the variable LV_WHEREARTis concatenated with others like AREA, REGION, TERRITORY and even though these concatenations might produce a valid Snowflake syntax, depending on the content of these variables, the final statement might also not work. For SnowConvert, there is no way to know if this will be valid or not since the content of such variables might be only available at runtime, it can come from other procedures, it can come from the database itself. So warnings are added to let the user know about this possibility.

Input Code:

replace PROCEDURE myProcedure
...

SET LV_WHEREART=' AND AREA_CODE = '''||AREA||'''' ;
SET LV_WHEREART=LV_WHEREART||' AND REGION_CODE = '''||REGION||'''' ;
SET LV_WHEREART=LV_WHEREART||' AND TERR_CODE = '''||TERRITORY||'''' ;

...
END

Output Code:

CREATE OR REPLACE PROCEDURE myProcedure ...
$$
...	

	// ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
	LV_WHEREART = ` AND AREA_CODE = '${AREA}'`;
	
	// ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
	LV_WHEREART = `${LV_WHEREART} AND REGION_CODE = '${REGION}'`;
	
	// ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
	LV_WHEREART = `${LV_WHEREART} AND TERR_CODE = '${TERRITORY}'`;	

...
$$;

Recommendations

• Check the MSCCP0004 complex pattern to learn more about how dynamic SQL is converted.

• Review the dynamically created statements to validate that they produce valid Snowflake syntax.

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