EXECUTE IMMEDIATE

Translation reference to convert Teradata EXECUTE IMMENDIATE statement to Snowflake Scripting

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

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

Snowflake

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

Execute Example

Teradata

Query

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

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

CREATE OR REPLACE PROCEDURE InsertProductInInventory (PRODUCTNAME STRING, PRICE FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
  LET insertStatement VARCHAR(100);
  insertStatement := 'INSERT INTO INVENTORY
VALUES (' || productName || ', ' || price || ')';
  --** MSC-ERROR - MSCCP0004 - 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|

Known Issues

No issues were found.

Related EWIs

1. MSCEWI1027: The following statement uses a variable/literal with an invalid query and it will not be executed.

2. MSCCP0004: The statement below has usages of dynamic SQL.