EXECUTE Statement [ESQL]

Quick Links:

Syntax

Syntax 1 – Executes the named dynamic statement that was previously prepared

EXECUTE <statement-name>
   ... [ { USING DESCRIPTOR <sqlda-name> | USING <host-variable-list> } ]
   ... [ { INTO DESCRIPTOR <into-sqlda-name> | INTO <into-host-variable-list >]
   ... [ ARRAY :<nnn> } ]

Syntax 2 – A short form to PREPARE and EXECUTE a statement that does not contain bind variables or output

EXECUTE IMMEDIATE <statement>

Parameters

(back to top)

statement-name identifier or host-variable

sqlda-name identifier

into-sqlda-name identifier

statement string or host-variable

USING OUTPUT from a SELECT statement or a CALL statement is put either into the variables in the variable list or into the program data areas described by the named SQLDA. The correspondence is one to one from the OUTPUT (selection list or parameters) to either the host variable list or the SQLDA descriptor array.
INTO if used with an INSERT statement, the inserted row is returned in the second descriptor. For example, when using autoincrement primary keys that generate primary-key values, EXECUTE provides a mechanism to refetch the row immediately and determine the primary-key value assigned to the row.
ARRAY used with prepared INSERT statements to allow wide inserts, which insert more than one row at a time and which might improve performance. The value nnn is the number of rows to be inserted. The SQLDA must contain nnn * (columns per row) variables. The first row is placed in SQLDA variables 0 to (columns per row)-1, and so on.

Examples

(back to top)

Example 1 executes a DELETE:

EXEC SQL EXECUTE IMMEDIATE
'DELETE FROM Employees WHERE EmployeeID = 105';

Example 2 executes a prepared DELETE statement:

EXEC SQL PREPARE del_stmt FROM
'DELETE FROM Employees WHERE EmployeeID = :a';
EXEC SQL EXECUTE del_stmt USING :employee_number;

Example 3 executes a prepared query:

EXEC SQL PREPARE sel1 FROM
'SELECT Surname FROM Employees WHERE EmployeeID = :a';
EXEC SQL EXECUTE sel1 USING :employee_number INTO :emp_lname;

Usage

(back to top)

Syntax 1 — If the dynamic statement contains host variable placeholders which supply information for the request (bind variables), then either the <sqlda-name> must specify a C variable which is a pointer to a SQLDA containing enough descriptors for all bind variables occurring in the statement, or the bind variables must be supplied in the <host-variable-list>.

Syntax 2 — The SQL statement contained in the string or host variable is immediately executed and is dropped on completion.

EXECUTE can be used for any SQL statement that can be prepared. Cursors are used for SELECT statements or CALL statements that return many rows from the database.

Note You cannot reference a Table UDF in an EXECUTE statement.

After successful execution of an INSERT, UPDATE, or DELETE statement, the sqlerrd[2] field of the SQLCA (SQLCOUNT) is filled in with the number of rows affected by the operation.

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Database products—Supported in Open Client/Open Server.

Permissions

(back to top)

Permissions are checked on the statement being executed.