Snowflake Documentation Home

EXEC Helper

EXEC function is a helper used to execute dynamic SQL inside a procedure.

You might also be interested in:

EXEC helper depends on IS NULL helper.

Syntax

EXEC(stmt) EXEC(stmt, binds[]) EXEC(stmt, opts{}) EXEC(stmt, binds[], 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.

opts (optional)

This is a Javascript object to describe how the values returned by the exec should be formated, this is used for SELECT statements.

Valid arguments for opts parameter

The following tables describe, how arguments should be sent to opts parameter in EXEC call:

Options when a query returns a single row

optsdescription

{ }

When opts is empty or not sent to exec call, the data will be returned inside an array.

{vars: 0}

This has the same effect as the default option. It will return the data inside an array.

{vars: 1}

This is used when a query returns just one column and one row. EXEC will return the value directly. This is equivalent to EXEC(stmt)[0]

{rec:recordVariable}

Used when you want to store the values returned by the query inside a record. Translation of records is described in Records translation reference. Record variable should be passed as an argument.

{row: 1}

This option returns a copy of ResultSet, this means that the object returned contains the methods described in ResultSet Snowflake documentation.

Options when a query returns multiple rows

optsDescription

{row:2}

With this option, it always returns a copy of the ResultSet regardless of the number of rows returned by the EXEC.

General options

optsDescription

{sql:0}

It makes sure that the SQL implicit Cursor attribute is not modified after executing the statement.

EXEC Helper Function Definition

var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
   arg = arg instanceof Date ? formatDate(arg) : IS_NULL(arg) ? null : arg;
   return arg;
};
var _RS, _ROWS, SQLERRM = "normal, successful completion", SQLCODE = 0;
var getObj = (_rs) => Object.assign(new Object(),_rs);
var getRow = (_rs) => (values = Object.values(_rs)) && (values = values.splice(-1 * _rs.getColumnCount())) && values;
var fetch = (_RS,_ROWS,fmode) => _RS.getRowCount() && _ROWS.next() && (fmode ? getObj : getRow)(_ROWS) || (fmode ? new Object() : []);

var EXEC = function (stmt,binds,opts) {
   try {
      binds = !(arguments[1] instanceof Array) && ((opts = arguments[1]) && []) || (binds || []);
      opts = opts || new Object();
      binds = binds ? binds.map(fixBind) : binds;
      _RS = snowflake.createStatement({
            sqlText : stmt,
            binds : binds
         });
      _ROWS = _RS.execute();
      if (opts.sql !== 0) {
         var isSelect = stmt.toUpperCase().trimStart().startsWith("SELECT");
         var affectedRows = isSelect ? _RS.getRowCount() : _RS.getNumRowsAffected();
         SQL.FOUND = affectedRows != 0;
         SQL.NOTFOUND = affectedRows == 0;
         SQL.ROWCOUNT = affectedRows;
      }
      if (opts.row === 2) {
         return _ROWS;
      }
      var INTO = function (opts) {
         if (opts.vars == 1 && _RS.getColumnCount() == 1 && _ROWS.next()) {
            return _ROWS.getColumnValue(1);
         }
         if (opts.rec instanceof Object && _ROWS.next()) {
            var recordKeys = Object.keys(opts.rec);
            Object.assign(opts.rec,Object.fromEntries(new Map(getRow(_ROWS).map((element,Index) => [recordKeys[Index],element]))))
            return opts.rec;
         }
         return fetch(_RS,_ROWS,opts.row);
      };
      var BULK_INTO_COLLECTION = function (into) {
         for(let i = 0;i < _RS.getRowCount();i++) {
            FETCH_INTO_COLLECTIONS(into,fetch(_RS,_ROWS,opts.row));
         }
         return into;
      };
      if (_ROWS.getRowCount() > 0) {
         return _ROWS.getRowCount() == 1 ? INTO(opts) : BULK_INTO_COLLECTION(opts);
      }
   } catch(error) {
      RAISE(error.code,error.name,error.message)
   }
};

Usage Samples

For all the samples, SnowConvert helpers Code were removed. You can find them here.

The following code examples illustrates how EXEC works.

EXEC simple case

Oracle

IN -> Oracle_01.sql