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

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

Snowflake Scripting

Execute

CALL PUBLIC.dummyMacro();