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> ... ] ) ]
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
, orsession_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 | +-------------+
This example executes a statement defined in a session variable:
SET stmt = $$ SELECT PI(); $$ ;EXECUTE IMMEDIATE $stmt; +-------------+ | PI() | |-------------| | 3.141592654 | +-------------+
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; $$;CALL execute_immediate_local_variable(); +----------------------------------+ | EXECUTE_IMMEDIATE_LOCAL_VARIABLE | |----------------------------------| | 240 | +----------------------------------+
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;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; $$ ;