EXECUTE/EXEC

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

Some parts in the output code are omitted for clarity reasons.

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

IN -> Teradata_01.sql

-- Additional Params: -t JavaScript
CREATE TABLE inventory (
    product_name VARCHAR(50),
    price INTEGER
);

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

Snowflake

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE inventory (
  product_name VARCHAR(50),
  price INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE PROCEDURE dummyMacro ()
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.
  
  INSERT_TEMP(`SELECT
   *
  FROM
   INVENTORY`,[]);
  return tablelist;
$$;

Execute prepared statement

Teradata

Execute

IN -> Teradata_02.sql

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

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE InsertProductInInventory (PRODUCTNAME VARCHAR(50), PRICE INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        dynamicSql CHAR(200);
    BEGIN
         
        dynamicSql := 'INSERT INTO INVENTORY
VALUES (?, ?)';
        !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PREPARE STATEMENT' NODE ***/!!!
            PREPARE preparedSql FROM dynamicSql;
        !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
        EXECUTE IMMEDIATE dynamicSql;
    END;
$$;

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

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

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

Execute macro statement

Teradata

Execute

IN -> Teradata_03.sql

EXECUTE dummyMacro;

Result

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

Snowflake Scripting

Execute

OUT -> Teradata_03.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE dummyMacro;

Related EWIs

1. SSC-EWI-0030: The statement below has usages of dynamic SQL.

2. SSC-EWI-0073: Pending Functional Equivalence Review.