EXECUTE IMMEDIATE

Translation reference to convert Teradata EXECUTE IMMENDIATE statement to Snowflake Scripting

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

Description

The Teradata EXECUTE IMMEDIATE statement allows the execution of dynamic SQL contained on variables or string literals.

For more information about EXECUTE IMMEDIATE click here.

-- EXECUTE IMMEDIATE syntax
EXECUTE IMMEDIATE <dynamic_statement>

<dynamic_statement> := {string_literal | string_variable}

Sample Source Patterns

Setup data

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

Teradata

IN -> Teradata_01.sql

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

Snowflake

OUT -> Teradata_01.sql

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

Execute Example

Teradata

Query

IN -> Teradata_02.sql

REPLACE PROCEDURE InsertProductInInventory(IN productName VARCHAR(50), IN price INTEGER)
BEGIN
	DECLARE insertStatement VARCHAR(100);
	SET insertStatement = 'INSERT INTO INVENTORY VALUES(' || productName || ', ' || price || ')';
    EXECUTE IMMEDIATE insertStatement;
END;

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

SELECT product_name, price FROM inventory;

Result

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

Snowflake Scripting

Query

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE InsertProductInInventory (PRODUCTNAME STRING, PRICE FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		LET insertStatement VARCHAR(100);
		insertStatement := 'INSERT INTO INVENTORY
VALUES (' || productName || ', ' || price || ')';
		!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
		EXECUTE IMMEDIATE insertStatement;
	END;
$$;

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

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

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

SELECT
	product_name,
	price FROM
	inventory;

Result

+--------------+-------+
| PRODUCT_NAME | PRICE |
+--------------+-------+
| Chocolate    | 75    |
+--------------+-------+
| Sugar        | 65    |
+--------------+-------+
| Rice         | 100   |
+--------------+-------+

Result

column1|column2                  |column3|
-------+-------------------------+-------+
      3|Mundo3                   |    3.3|

Related EWIS

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