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
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE EXECUTE_PROC AS
BEGIN
  --CREATES HARDWARE TABLE WITH COLUMNS ID, DEVICE AND COLOR
  --THIS IS AN EXECUTE IMMEDIATE JUST WITH AN STATEMENT
  EXECUTE IMMEDIATE 'CREATE TABLE HARDWARE (ID NUMBER, DEVICE VARCHAR2(15), COLOR VARCHAR(15))';
END;

Snowflake

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

  //CREATES HARDWARE TABLE WITH COLUMNS ID, DEVICE AND COLOR
  //THIS IS AN EXECUTE IMMEDIATE JUST WITH AN STATEMENT
  EXEC(`CREATE OR REPLACE TABLE HARDWARE (ID NUMBER(38, 18),
   DEVICE VARCHAR(15),
   COLOR VARCHAR(15))`);
$$;

EXEC with bindings

Oracle

IN -> Oracle_02.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE EXECUTE_PROC AS 
  ID_VAR NUMBER;
  DEVICE_VAR VARCHAR2(15);
  DEV_COLOR  V