EXECUTE IMMEDIATE | Snowflake Documentation (original) (raw)

Executes a string that contains a SQL statement or aSnowflake 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 '' [ USING ( [ , ... ] ) ]

EXECUTE IMMEDIATE [ USING ( [ , ... ] ) ]

EXECUTE IMMEDIATE $ [ USING ( [ , ... ] ) ]

Required parameters¶

'_stringliteral_' or . _variable_ or . _sessionvariable_

A string literal, Snowflake Scripting variable, orsession 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 orbranching statement)
• A block

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

Optional parameters¶

USING ( _bindvariable_ [ , _bindvariable_ ... ] )

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 _stringliteral_, _variable_, or _sessionvariable_ must contain only one statement. (A block is considered one statement, even if the body of the block contains multiple statements.)
• A _sessionvariable_ 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 aSnowflake 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;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

CREATE PROCEDURE execute_immediate_local_variable() RETURNS VARCHAR AS DECLAREv1VARCHARDEFAULT′CREATETABLEtemporary1(iINTEGER)′;v2VARCHARDEFAULT′INSERTINTOtemporary1(i)VALUES(76)′;resultINTEGERDEFAULT0;BEGINEXECUTEIMMEDIATEv1;EXECUTEIMMEDIATEv2∣∣′,(80)′∣∣′,(84)′;result:=(SELECTSUM(i)FROMtemporary1);RETURNresult::VARCHAR;END;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;DECLAREv1VARCHARDEFAULT′CREATETABLEtemporary1(iINTEGER)′;v2VARCHARDEFAULT′INSERTINTOtemporary1(i)VALUES(76)′;resultINTEGERDEFAULT0;BEGINEXECUTEIMMEDIATEv1;EXECUTEIMMEDIATEv2∣∣′,(80)′∣∣′,(84)′;result:=(SELECTSUM(i)FROMtemporary1);RETURNresult::VARCHAR;END;;

Call the stored procedure:

CALL execute_immediate_local_variable();

+----------------------------------+

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);

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;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or theexecute_stream or execute_string method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

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 DECLARErsRESULTSET;queryVARCHARDEFAULT′SELECT∗FROMinvoicesWHEREprice>?ANDprice<?′;BEGINrs:=(EXECUTEIMMEDIATE:queryUSING(minimumprice,maximumprice));RETURNTABLE(rs);END;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;DECLARErsRESULTSET;queryVARCHARDEFAULT′SELECT∗FROMinvoicesWHEREprice>?ANDprice<?′;BEGINrs:=(EXECUTEIMMEDIATE:queryUSING(minimump​rice,maximump​rice));RETURNTABLE(rs);END; ;

Call the stored procedure:

CALL min_max_invoices_sp(20, 30);

+----+-------+ | 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 = SELECTPI();SELECT PI();SELECTPI(); ;

+-------------+

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, seeUsing Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector.

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

EXECUTE IMMEDIATE DECLAREradiusofcircleFLOAT;areaofcircleFLOAT;BEGINradiusofcircle:=3;areaofcircle:=PI()∗radiusofcircle∗radiusofcircle;RETURNareaofcircle;END;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;DECLAREradiuso​fc​ircleFLOAT;areao​fc​ircleFLOAT;BEGINradiuso​fc​ircle:=3;areao​fc​ircle:=PI()∗radiuso​fc​ircle∗radiuso​fc​ircle;RETURNareao​fc​ircle;END; ;

+-----------------+