Links

MSCEWI1022

Severity

Low

Description

This error is used to report that one or more identifiers in a specific statement are considered parameters by default.

Example Code

Input Code:

CREATE MACRO SAME_MACRO_COLUMN_AND_PARAMATERS (
LOAD_USER_ID (VARCHAR (32), CHARACTER SET LATIN),
UPDATE_USER_ID (VARCHAR (32), CHARACTER SET LATIN)
) AS (
UPDATE TABLE1 SET LOAD_USER_ID = :LOAD_USER_ID, UPDATE_USER_ID = :UPDATE_USER_ID;
INSERT INTO TABLE1 (LOAD_USER_ID, UPDATE_USER_ID) VALUES (:LOAD_USER_ID, :UPDATE_USER_ID);
DELETE FROM TABLE1 WHERE :LOAD_USER_ID = LOAD_USER_ID;
);

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.PUBLIC.SAME_MACRO_COLUMN_AND_PARAMATERS (LOAD_USER_ID VARCHAR (32), UPDATE_USER_ID VARCHAR (32))
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, ACTIVITY_COUNT, INTO;
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? arg.toISOString() : arg;
return arg;
};
var EXEC = function (stmt,binds,noCatch,catchFunction) {
try {
binds = binds ? binds.map(fixBind) : binds;
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
ACTIVITY_COUNT = _RS.getNumRowsAffected();
if (INTO) return {
INTO : function () {
return INTO();
}
};
} catch(error) {
MESSAGE_TEXT = error.message;
SQLCODE = error.code;
SQLSTATE = error.state;
var msg = `ERROR CODE: ${SQLCODE} SQLSTATE: ${SQLSTATE} MESSAGE: ${MESSAGE_TEXT}`;
if (catchFunction) catchFunction(error);
if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error); else throw new Error(msg);
}
};
EXEC(`UPDATE PUBLIC.TABLE1 SET LOAD_USER_ID = ?, UPDATE_USER_ID = ?`,[LOAD_USER_ID,UPDATE_USER_ID]);
// ** MSC-ERROR - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`INSERT INTO PUBLIC.TABLE1 (LOAD_USER_ID, UPDATE_USER_ID) VALUES (?, ?)`,[LOAD_USER_ID,UPDATE_USER_ID]);
EXEC(`DELETE FROM PUBLIC.TABLE1 WHERE ? = LOAD_USER_ID`,[LOAD_USER_ID]);
$$;

Recommendation

  • Make sure all the dependencies(tables and views) related to the procedure statement are being migrated.
  • 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.