EXECUTE IMMEDIATE

The EXECUTE IMMEDIATE statement dynamically compiles and executes an SQL statement. You can use this statement only in SQL that is embedded in a program.

Authorization

To issue an EXECUTE IMMEDIATE statement, you must have the privileges required to issue the statement being dynamically compiled and executed.

Syntax

►►─── EXECUTE IMMEDIATE ─┬─ 'sql-statement' ──────────────┬───────────────────►◄
                         └─ :sql-statement-variable-name ─┘

Parameters

'sql-statement': Specifies an SQL statement that can be compiled and executed immediately. Sql-statement must be enclosed in single quotation marks. Do not include the SQL prefix or terminator within the statement text.
:sql-statement-variable-name: Identifies a host variable, local variable, or routine parameter containing the statement to be compiled and executed immediately. Sql-statement-variable-name must be a variable previously declared in the application program or SQL routine. It must be defined as an elementary data item with no sub-elements. If sql-statement-variable-name is a local variable or routine parameter, the colon must not be coded.

Usage

Statements Eligible for Immediate Execution

The following SQL statements can be compiled and executed immediately:

All access module management, authorization, logical data description, session management, and transaction management statements
DELETE
INSERT
UPDATE

Additionally, all CA IDMS utility and physical data description statements can be compiled and executed immediately.

No Host Variables, Local Variables, or Routine Parameters in a Dynamically Compiled Statement

An SQL statement that is to be compiled dynamically cannot include any host variables, local variables, or routine parameters.

No Dynamic Parameters

An SQL statement that is compiled using the EXECUTE IMMEDIATE statement cannot include any dynamic parameter markers.

Note: For more information about the dynamic compilation of SQL statements, see the CA IDMS SQL Programming Guide.

Example

Using a Variable in EXECUTE

The following EXECUTE IMMEDIATE statement directs CA IDMS to dynamically compile and execute the statement contained in the variable DYN-INSERT:

EXEC SQL
   EXECUTE IMMEDIATE :DYN-INSERT
END-EXEC