EXECUTE IMMEDIATE¶

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

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 a string literal¶

This example executes a statement defined in a string literal:

EXECUTE IMMEDIATE 'SELECT PI()';

Copy

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

Executing a statement in a session variable¶

This example executes a statement defined in a session variable:

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

Copy

EXECUTE IMMEDIATE $stmt;

Copy

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

Executing statements with 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 execute_immediate_local_variable();

Copy

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

Running an anonymous block in SnowSQL or 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 |
+-----------------+

Executing a statement with bind variables¶

This example uses EXECUTE IMMEDIATE to execute a SELECT statement that contains bind variables in the USING parameter in a Snowflake Scripting block. 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

Now run the block:

DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

Copy

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

Copy

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