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;
	};

Exec Usage Sample

Teradata

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

CREATE OR REPLACE PROCEDURE PUBLIC.ProcedureSample ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
 	// REGION SnowConvert Helpers Code
	switch(value) {
	   case 0:EXEC(`SELECT * FROM PUBLIC.table1`,[]);
	   break;
	   default:EXEC(`UPDATE PUBLIC.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);
	   }
	};