Links

MSCEWI1027

The following statement uses a variable/literal with an invalid query and it will not be executed.

Severity

Medium

Description

This warning is used to report that a specific statement uses a variable or literal with an invalid query and for that reason, it will not be executed.

Example Code

Input Code:

REPLACE PROCEDURE TEST.COLLECT_STATS
(
)
BEGIN
COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);
SET STATS_STATEMENT = 'COLLECT STATS ON ' || OUT_DB || '.' || OUT_TBL || ' COLUMN(' || C4.ColumnName || ');';
EXECUTE IMMEDIATE STATS_STATEMENT;
EXECUTE IMMEDIATE 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);';
SET STATS_STATEMENT_NOT_DYNAMIC = 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);';
EXECUTE IMMEDIATE STATS_STATEMENT_NOT_DYNAMIC;
END;
;

Output Code:

The warning message appears every time an Execute uses a statement (variable or literal) with an invalid query. In the example, those lines are 15, 18, and 22.
CREATE OR REPLACE PROCEDURE TEST.PUBLIC.COLLECT_STATS
(
)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
...
// END REGION
STATS_STATEMENT = `COLLECT STATS ON ${OUT_DB}.${OUT_TBL} COLUMN(${C4.ColumnName})`;
/* ** MSC-WARNING - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ** */
/* EXECUTE IMMEDIATE STATS_STATEMENT */
;
/* ** MSC-WARNING - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ** */
/* EXECUTE IMMEDIATE 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);' */
;
STATS_STATEMENT_NOT_DYNAMIC = `COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME)`;
/* ** MSC-WARNING - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ** */
/* EXECUTE IMMEDIATE STATS_STATEMENT_NOT_DYNAMIC */
;
$$;

Recommendation

Last modified 11mo ago