EXECUTE IMMEDIATE statement
Use the EXECUTE IMMEDIATE statement to perform tasks equivalent to what the PREPARE, EXECUTE, and FREE statements accomplish, but as a single operation.
Use this Dynamic SQL statement with and SPL.
Syntax
- 1 ESQL/C only
- 2 SPL only
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| char_expr | Expression that evaluates to a character data type | Must evaluate to a CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR data type | Expression |
| statement | Text of a valid SQL statement | See the same sections that are listed below for statement_var | See this chapter. |
| statement _var | Variable containing statement or (in ESQL/C) a semicolon-separated list of statements | Must be a previously declared variable of type CHAR, NCHAR, NVARCHAR, or VARCHAR (or in SPL, LVARCHAR). See EXECUTE IMMEDIATE and Restricted Statements and Restrictions on Valid Statements. | Language specific |
Usage
The EXECUTE IMMEDIATE statement dynamically executes a single SQL statement (or in ESQL/C routines, a semicolon-separated list of SQL statements) that is constructed during program execution. For example, you can obtain the name of a database from program input, construct the DATABASE statement as a program variable, and then use EXECUTE IMMEDIATE to execute the statement, which opens the specified database.
Within ESQL/C routines, the statement text specified by the variable or quoted string can include more than one SQL statement, if consecutive statements are separated by a semicolon ( ; ) delimiter. In SPL routines, however, only one statement can be included. The statement cannot be an SPL statement, but can be any SQL statement that is not listed in the sections EXECUTE IMMEDIATE and Restricted Statements or Restrictions on Valid Statements.
The specification that follows the IMMEDIATE keyword, if valid, is parsed and executed; then all data structures and memory resources are released immediately. Unless you use EXECUTE IMMEDIATE, these operations would otherwise require separate PREPARE, EXECUTE, and FREE statements.
The session environment values (such as the EXTDIRECTIVES, OPTCOMPIND, or USELASTCOMMITTED settings of the ESQL/C or SPL routine that issues the EXECUTE IMMEDIATE statement) override the corresponding ONCONFIG parameter values, if these are different.
In ANSI/ISO-compliant databases that support implicit transactions, the EXECUTE IMMEDIATE statement does not, by default, begin a new transaction. Execution of the specified SQL statement text, however, can begin a new transaction.