EXECUTE IMMEDIATE Statement [ESQL] [SP]

Extends the range of statements that can be executed from within procedures. It lets you execute dynamically prepared statements, such as statements that are constructed using the parameters passed in to a procedure.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

Syntax 1

EXECUTE IMMEDIATE [ execute-option ] string-expression

execute-option:
   WITH QUOTES [ ON | OFF ]
   | WITH ESCAPES { ON | OFF } 
   | WITH RESULT SET { ON | OFF }

Syntax 2

EXECUTE ( string-expression )

Parameters

(back to top)

WITH QUOTES [ON] – any double quotes in the string expression are assumed to delimit an identifier. When not specified, the treatment of double quotes in the string expression depends on the current setting of the QUOTED_IDENTIFIER database option.
WITH QUOTES is useful when an object name that is passed into the stored procedure is used to construct the statement that is to be executed, but the name might require double quotes and the procedure might be called when QUOTED_IDENTIFIER is set to OFF.

See QUOTED_IDENTIFIER Option [TSQL].

WITH ESCAPES – causes any escape sequences (such as \n, \x, or \\) in the string expression to be ignored. For example, two consecutive backslashes remain as two backslashes, rather than being converted to a single backslash. The default setting is ON.
You can use WITH ESCAPES OFF for easier execution of dynamically constructed statements referencing file names that contain backslashes.

string-expression – in some contexts, escape sequences in the string-expression are transformed before EXECUTE IMMEDIATE is executed. For example, compound statements are parsed before being executed, and escape sequences are transformed during this parsing, regardless of the WITH ESCAPES setting. In these contexts, WITH ESCAPES OFF prevents further translations from occurring. For example:
```
BEGIN
DECLARE String1 LONG VARCHAR;
DECLARE String2 LONG VARCHAR;
EXECUTE IMMEDIATE 
 'SET String1 = ''One backslash: \\\\ '''; 
 EXECUTE IMMEDIATE WITH ESCAPES OFF 
 'SET String2 = ''Two backslashes: \\\\ ''';  
 SELECT String1, String2 
END
```

WITH RESULT SET – when specified with ON, the EXECUTE IMMEDIATE statement returns a result set. With this clause, the containing procedure is marked as returning a result set. If you do not include this clause, an error is reported when the procedure is called if the statement does not produce a result set.
Note: The default option is OFF, meaning that no result set is produced when the statement is executed.

Examples

(back to top)

Example 1 – creates a table, where the table name is supplied as a parameter to the procedure. The full EXECUTE IMMEDIATE statement must be on a single line.

CREATE PROCEDURE CreateTableProc(
            IN tablename char(30)
            )
BEGIN
 EXECUTE IMMEDIATE 'CREATE TABLE ' || tablename ||
' ( column1 INT PRIMARY KEY)'
END;

Call the procedure and create table mytable:

CALL CreateTableProc( 'mytable' )

Usage

(back to top)

Literal strings in the statement must be enclosed in single quotes, and must differ from any existing statement name in a PREPARE or EXECUTE IMMEDIATE statement. The statement must be on a single line.

Only global variables can be referenced in a statement executed by EXECUTE IMMEDIATE.

Only syntax 2 can be used inside Transact-SQL stored procedures.

Side Effects:

None. However, if the statement is a data definition statement with an automatic commit as a side effect, then that commit does take place.

Standards

(back to top)

SQL—ISO/ANSI SQL compliant.
SAP Sybase Database product—Supported in Open Client/Open Server.

Permissions

(back to top)

None. The statement is executed with the permissions of the owner of the procedure, not with the permissions of the user who calls the procedure.