Exec Helper

The exec helper is a function used to execute SQL statements in procedures.

Syntax

EXEC(stmt) EXEC(stmt, binds) EXEC(stmt, binds, noCatch) EXEC(stmt, binds, noCatch, catchFunction) EXEC(stmt, binds, noCatch, catchFunction, opts)

Parameters

stmt

The string of the SQL statement to execute.

binds (optional)

An array with the values or the variables to bind into the SQL statement.

NoCatch (optional)

Boolean to know if an error should not be catched.

catchFunction (optional)

A function to execute in case an error occurs during the execution of the exec function.

opts (optional)

A JSON object ({ temp : true }) to know if the query ID should be returned.

FixBind And FormatDate Functions

The Exec helper uses a function defined in the helpers called FixBind. This function uses the FormatDate function when it encounters that one of the binding variables is a date type, this is done to manage properly the date types in Snowflake. Both functions are defined as below.

var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
	var fixBind = function (arg) {
	   arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
	   return arg;
	};

Some parts of the output code are omitted for clarity reasons.

Exec Usage Sample

Teradata

IN -> Teradata_01.sql

-- Additional Params: -t JavaScript
REPLACE PROCEDURE ProcedureSample ()
BEGIN

case value
when 0 then
  select * from table1
else
  update table1 set name = "SpecificValue" where id = value;
end case

END;

Snowflake output

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE ProcedureSample ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert Helpers Code section is omitted.

  switch(value) {
    case 0:EXEC(`SELECT * from table1`,[]);
    break;
    default:EXEC(`UPDATE table1
    set
      name = "SpecificValue"
    where
      id = value`,[]);
    break;
  }
$$;

Exec Helper Definition

var EXEC = function (stmt,binds,noCatch,catchFunction,opts) {
	   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();
	      HANDLE_NOTFOUND && HANDLE_NOTFOUND(_RS);
	      if (INTO) return {
	         INTO : function () {
	            return INTO();
	         }
	      };
			  if (_OUTQUERIES.length < DYNAMIC_RESULTS) _OUTQUERIES.push(_ROWS.getQueryId());
	      if (opts && opts.temp) return _ROWS.getQueryId();
	   } 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);
	   }
	};