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
-- 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
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);
}
};
Known Issues
No issues were found.
Related EWIs
No related EWIs.
Last updated