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¶

Basic Examples¶

This example executes a statement defined in a string literal:

EXECUTE IMMEDIATE 'SELECT PI()';
+-------------+
|        PI() |
|-------------|
| 3.141592654 |
+-------------+
Copy

This example executes a statement defined in a session variable:

SET stmt =
$$
    SELECT PI();
$$
;
Copy
EXECUTE IMMEDIATE $stmt;
+-------------+
|        PI() |
|-------------|
| 3.141592654 |
+-------------+
Copy

This example executes statements that are defined in two local variables. This 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
CALL execute_immediate_local_variable();
+----------------------------------+
| EXECUTE_IMMEDIATE_LOCAL_VARIABLE |
|----------------------------------|
| 240                              |
+----------------------------------+
Copy

This example uses EXECUTE IMMEDIATE to execute a SELECT statement that contains bind parameters.

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

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

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
Language: English