EXECUTE IMMEDIATE
Applies to: Databricks SQL Databricks Runtime 14.3 and above
Executes a SQL statement provided as a STRING
.
The statement optionally passes arguments to parameter markers and assigns the results to variables.
Syntax
EXECUTE IMMEDIATE sql_string
[ INTO var_name [, ...] ]
[ USING { arg_expr [ AS ] [alias] } [, ...] ]
For compatibility with other SQL dialects, EXECUTE IMMEDIATE
also supports USING ( { arg_expr [ AS ] [alias] } [, ...] )
Parameters
sql_string
A
STRING
literal or variable, producing a well-formed SQL statement.You cannot nest
EXECUTE IMMEDIATE
statements.INTO ( var_name [, ...] )
Optionally returns the results of a single row query into SQL variables. If the query returns no rows the result is
NULL
.If the statement is not a query, Azure Databricks raises
INVALID_STATEMENT_FOR_EXECUTE_INTO
error.If the query returns more than one row, Azure Databricks raises ROW_SUBQUERY_TOO_MANY_ROWS error.
-
A SQL variable. A variable may not be referenced more than once.
-
USING { arg_expr [ AS ] [alias] } [, ...]
Optionally, if
sql_string
contains parameter markers, binds in values to the parameters.arg_expr
A literal or variable that binds to a parameter marker. If the parameter markers are unnamed, the binding is by position. For named parameter markers, binding is by name.
alias
Overrides the name used to bind
arg_expr
to a named parameter marker. Each named parameter marker must be matched once. Not allarg_expr
must be matched.
Examples
-- A self-contained execution using a literal string
> EXECUTE IMMEDIATE 'SELECT SUM(c1) FROM VALUES(?), (?) AS t(c1)' USING 5, 6;
11
-- A SQL string composed in a SQL variable
> DECLARE sqlStr = 'SELECT SUM(c1) FROM VALUES(?), (?) AS t(c1)';
> DECLARE arg1 = 5;
> DECLARE arg2 = 6;
> EXECUTE IMMEDIATE sqlStr USING arg1, arg2;
11
-- Using the INTO clause
> DECLARE sum INT;
> EXECUTE IMMEDIATE sqlStr INTO sum USING arg1, arg2;
> SELECT sum;
11
-- Using named parameter markers
> SET VAR sqlStr = 'SELECT SUM(c1) FROM VALUES(:first), (:second) AS t(c1)';
> EXECUTE IMMEDIATE sqlStr INTO sum
USING (5 AS first, arg2 AS second);
> SELECT sum;
11