Links

MSCEWI1038

Dynamic SQL statement may be unrecognized.

Severity

Low

Description

This warning is added to let the user know about dynamically created statements that might not contain a 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 the 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}'`;
...
$$;

Recommendation

  • Review the dynamically created statements to validate that they produce valid Snowflake syntax.
  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs