EXECUTE/EXEC
Translation reference to convert Teradata EXECUTE or EXEC statement to Snowflake Scripting
Description
The Teradata EXECUTE
statement 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.
-- Additional Params: -t JavaScript
CREATE TABLE inventory (
product_name VARCHAR(50),
price INTEGER
);
CREATE MACRO dummyMacro AS(
SELECT * FROM INVENTORY;
);
Execute prepared statement
Teradata
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
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 dummyMacro;
Snowflake Scripting
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE dummyMacro;
Related EWIs
SSC-EWI-0030: The statement below has usages of dynamic SQL.
SSC-EWI-0073: Pending Functional Equivalence Review.
Last updated