EXECUTE/EXEC

Translation reference to convert Teradata EXECUTE or EXEC statement to Snowflake Scripting

Description

The Teradata EXECUTEstatement allows the execution prepared dynamic SQL or macros, on the other hand exec only allows macros.

For more information regarding Teradata EXECUTE/EXEC, check Macro Form and Dynamic SQL Form

-- EXECUTE macro syntax
{EXECUTE | EXEC } macro_identifier [ (<parameter_definition>[, ...n] ) ] [;]  

<parameter_definition>:= {parameter_name = constant_expression | constant_expresion}


-- EXECUTE prepared dynamic SQL syntax
EXECUTE prepare_indentifier [<using>|<usingDescriptor>]

<using>:= USING < host_variable >[, ...n]
<host_variable>:= [:] host_variable_name [[INDICATOR] :host_indicator_name]
<usingDescriptor>:= USING DESCRIPTOR [:] descript_area

Sample Source Patterns

Setup data

The following code is necessary to execute the sample patterns present in this section.

Teradata

CREATE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
);

CREATE MACRO dummyMacro AS(
  SELECT * FROM INVENTORY;
);

Snowflake

CREATE TABLE PUBLIC.inventory (
product_name VARCHAR(50),
price INTEGER
);

CREATE OR REPLACE PROCEDURE PUBLIC.dummyMacro ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
 	// REGION SnowConvert Helpers Code
	var HANDLE_NOTFOUND;
	var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, ACTIVITY_COUNT = 0, INTO, _OUTQUERIES = [], DYNAMIC_RESULTS = -1;
	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;
	};
	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);
	   }
	};
	var procname = `PUBLIC.dummyMacro`;
	var temptable_prefix, tablelist = [];
	var INSERT_TEMP = function (query,parameters) {
	   if (!temptable_prefix) {
	      var sql_stmt = `select current_session() || '_' || to_varchar(current_timestamp, 'yyyymmddhh24missss')`;
	      var rs = snowflake.createStatement({
	         sqlText : sql_stmt,
	         binds : []
	      }).execute();
	      temptable_prefix = rs.next() && (procname + '_TEMP_' + rs.getColumnValue(1) + '_');
	   }
	   var tablename = temptable_prefix + tablelist.length;
	   tablelist.push(tablename);
	   var sql_stmt = `CREATE OR REPLACE TEMPORARY TABLE ${tablename} AS ${query}`;
	   snowflake.execute({
	      sqlText : sql_stmt,
	      binds : parameters
	   });
	   return tablename;
	};
	// END REGION
	
	INSERT_TEMP(`SELECT
	*
	FROM PUBLIC.INVENTORY`,[]);
	return tablelist;
 
$$;

Execute prepared statement

Teradata

Execute

CREATE PROCEDURE InsertProductInInventory(IN productName VARCHAR(50), IN price INTEGER)
BEGIN
    DECLARE dynamicSql CHAR(200);
    SET dynamicSql = 'INSERT INTO INVENTORY VALUES( ?, ?)';
    PREPARE preparedSql FROM dynamicSql;
    EXECUTE preparedSql USING productName, price;
    
END;

CALL InsertProductInInventory('''Chocolate''', 75);
CALL InsertProductInInventory('''Sugar''', 65);
CALL InsertProductInInventory('''Rice''', 100);

Snowflake Scripting

Execute

CREATE OR REPLACE PROCEDURE PUBLIC.InsertProductInInventory (PRODUCTNAME STRING, PRICE FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      LET dynamicSql CHAR(200);
      dynamicSql := 'INSERT INTO PUBLIC.INVENTORY VALUES ( ?, ?)';
      /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PREPARE STATEMENT' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*    PREPARE preparedSql FROM dynamicSql;*/
       
      EXECUTE IMMEDIATE dynamicSql;
   END;
$$;

CALL InsertProductInInventory('''Chocolate''', 75);

CALL InsertProductInInventory('''Sugar''', 65);

CALL InsertProductInInventory('''Rice''', 100);

Execute macro statement

Teradata

Execute

EXECUTE dummyMacro;

Result

+---------------+-------+
| product_name  | price |
+---------------+-------+
| 'Chocolate'   | 75    |
+---------------+-------+
| 'Sugar'       | 65    |
+---------------+-------+
| 'Rice'        | 100   |
+---------------+-------+

Snowflake Scripting

Execute

CALL PUBLIC.dummyMacro();