Comment on page
MSCEWI1038
Dynamic SQL statement may be unrecognized.
Low
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
In the following example, it can be seen that the variable
LV_WHEREART
is 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. 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
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}'`;
...
$$;
- Review the dynamically created statements to validate that they produce valid Snowflake syntax.