---

layout: global title: EXECUTE IMMEDIATE displayTitle: EXECUTE IMMEDIATE license: | Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Description

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 constant expression of type STRING, producing a well-formed SQL statement.

• 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, Spark raises INVALID_STATEMENT_FOR_EXECUTE_INTO error.

  If the query returns more than one row, Spark raises ROW_SUBQUERY_TOO_MANY_ROWS error.

  • var_name

    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 constant expression 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 all arg_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;
DECLARE sqlStr = 'SELECT SUM(c1) FROM VALUES(:first), (:second) AS t(c1)';
EXECUTE IMMEDIATE sqlStr INTO sum USING arg1, arg2;
SELECT sum;
 11

-- Using named parameter markers
DECLARE sum INT;
DECLARE 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

-- Using constant expressions
DECLARE foo = 'sum';
EXECUTE IMMEDIATE 'SELECT ' || foo || '(c1) FROM VALUES(?), (?) AS t(c1)'
   USING 5 + 6, 7 + length('hello');
 33