MSCEWI1022

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

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]);
 
$$;

Recommendations

• Make sure all the dependencies(tables and views) related to the procedure statement are being migrated.

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