EXECUTE IMMEDIATE¶

Executes a string that contains a SQL statement or a Snowflake Scripting statement.

You can use EXECUTE IMMEDIATE to do the following:

In a Snowflake Scripting block, execute dynamic SQL, where parts of the SQL statement aren’t known until runtime. For examples, see Executing dynamic SQL in a Snowflake Scripting block.
Set a session variable to a SQL statement, and reference the session variable to run the SQL statement. For an example, see Setting a session variable to a statement and executing it.
If you are using SnowSQL or the Classic Console, run a Snowflake Scripting anonymous block. For an example, see Running an anonymous block in SnowSQL or the Classic Console.

Syntax¶

EXECUTE IMMEDIATE '<string_literal>'
    [ USING ( <bind_variable> [ , <bind_variable> ... ] ) ]

EXECUTE IMMEDIATE <variable>
    [ USING ( <bind_variable> [ , <bind_variable> ... ] ) ]

EXECUTE IMMEDIATE $<session_variable>
    [ USING ( <bind_variable> [ , <bind_variable> ... ] ) ]

Copy

Required parameters¶

'string_literal' or . variable or . session_variable

A string literal, Snowflake Scripting variable, or session variable that contains a statement. A statement can be any of the following:

A single SQL statement
A stored procedure call
A control-flow statement (for example, looping or branching statement)
A block

If you use a session variable, the length of the statement must not exceed the maximum size of a session variable (256 bytes).

Optional parameters¶

USING ( bind_variable [ , bind_variable ... ] ): Specifies one or more bind variables that hold values to be used in the cursor’s query definition (for example, in a WHERE clause).

Returns¶

EXECUTE IMMEDIATE returns the result of the executed statement. For example, if the string or variable contained a SELECT statement, the result set of the SELECT statement is returned.

Usage notes¶

The string_literal, variable, or session_variable must contain only one statement. (A block is considered one statement, even if the body of the block contains multiple statements.)
A session_variable must be preceded by a dollar sign ($).
A local variable must not be preceded by a dollar sign ($).

Examples¶

The following are examples that use the EXECUTE IMMEDIATE command.

Executing dynamic SQL in a Snowflake Scripting block¶

The following examples execute dynamic SQL in a Snowflake Scripting block.

Executing statements that contain variables¶

This example executes statements that are defined in two local variables in a Snowflake Scripting stored procedure. This example also demonstrates that EXECUTE IMMEDIATE works not only with a string literal, but also with an expression that evaluates to a string (VARCHAR).

CREATE PROCEDURE execute_immediate_local_variable()
RETURNS VARCHAR
AS
DECLARE
  v1 VARCHAR DEFAULT 'CREATE TABLE temporary1 (i INTEGER)';
  v2 VARCHAR DEFAULT 'INSERT INTO temporary1 (i) VALUES (76)';
  result INTEGER DEFAULT 0;
BEGIN
  EXECUTE IMMEDIATE v1;
  EXECUTE IMMEDIATE v2  ||  ',(80)'  ||  ',(84)';
  result := (SELECT SUM(i) FROM temporary1);
  RETURN result::VARCHAR;
END;

Copy

Note: If you are using SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):

CREATE PROCEDURE execute_immediate_local_variable()
RETURNS VARCHAR
AS
$$
DECLARE
  v1 VARCHAR DEFAULT 'CREATE TABLE temporary1 (i INTEGER)';
  v2 VARCHAR DEFAULT 'INSERT INTO temporary1 (i) VALUES (76)';
  result INTEGER DEFAULT 0;
BEGIN
  EXECUTE IMMEDIATE v1;
  EXECUTE IMMEDIATE v2  ||  ',(80)'  ||  ',(84)';
  result := (SELECT SUM(i) FROM temporary1);
  RETURN result::VARCHAR;
END;
$$;

Copy

Call the stored procedure:

CALL execute_immediate_local_variable();

Copy

+----------------------------------+
| EXECUTE_IMMEDIATE_LOCAL_VARIABLE |
|----------------------------------|
| 240                              |
+----------------------------------+

Executing a statement that contains bind variables¶

This example uses EXECUTE IMMEDIATE to execute a SELECT statement that contains bind variables in the USING parameter in a Snowflake Scripting stored procedure. First create the table and insert the data:

CREATE OR REPLACE TABLE invoices (id INTEGER, price NUMBER(12, 2));

INSERT INTO invoices (id, price) VALUES
  (1, 11.11),
  (2, 22.22);

Copy

Create the stored procedure:

CREATE OR REPLACE PROCEDURE min_max_invoices_sp(
    minimum_price NUMBER(12,2),
    maximum_price NUMBER(12,2))
  RETURNS TABLE (id INTEGER, price NUMBER(12, 2))
  LANGUAGE SQL
AS
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

Copy

CREATE OR REPLACE PROCEDURE min_max_invoices_sp(
    minimum_price NUMBER(12,2),
    maximum_price NUMBER(12,2))
  RETURNS TABLE (id INTEGER, price NUMBER(12, 2))
  LANGUAGE SQL
AS
$$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

Copy

Call the stored procedure:

CALL min_max_invoices_sp(20, 30);

Copy

+----+-------+
| ID | PRICE |
|----+-------|
|  2 | 22.22 |
+----+-------+

Setting a session variable to a statement and executing it¶

This example executes a statement defined in a session variable:

SET stmt =
$$
    SELECT PI();
$$
;

Copy

EXECUTE IMMEDIATE $stmt;

Copy

+-------------+
|        PI() |
|-------------|
| 3.141592654 |
+-------------+

Running an anonymous block in SnowSQL or the Classic Console¶

When you run a Snowflake Scripting anonymous block in SnowSQL or the Classic Console, you must specify the block as a string literal (delimited by single quotes or double dollar signs), and you must pass the block to the EXECUTE IMMEDIATE command. For more information, see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector.

This example runs an anonymous block passed to the EXECUTE IMMEDIATE command:

EXECUTE IMMEDIATE $$
DECLARE
  radius_of_circle FLOAT;
  area_of_circle FLOAT;
BEGIN
  radius_of_circle := 3;
  area_of_circle := PI() * radius_of_circle * radius_of_circle;
  RETURN area_of_circle;
END;
$$
;

Copy

+-----------------+
| anonymous block |
|-----------------|
|    28.274333882 |
+-----------------+