ASA SQL Reference
  SQL Statements

EXECUTE IMMEDIATE statement [SP]

Description 

Use this statement to enable dynamically-constructed statements to be executed from within a procedure.

Syntax 1 

EXECUTE IMMEDIATE [ execute-option ] string-expression

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

Syntax 2 

EXECUTE ( string-expression )

Parameters 

WITH QUOTES    When you specify WITH QUOTES or WITH QUOTES ON, any double quotes in the string expression are assumed to delimit an identifier. When you do not specify WITH QUOTES, or specify WITH QUOTES OFF, the treatment of double quotes in the string expression depends on the current setting of the QUOTED_IDENTIFIER 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.

For more information, see the QUOTED_IDENTIFIER option [compatibility].

WITH ESCAPES    WITH ESCAPES OFF 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 equivalent to WITH ESCAPES ON.

One use of WITH ESCAPES OFF is for easier execution of dynamically-constructed statements referencing filenames that contain backslashes.

In some contexts, escape sequences in the string-expression are transformed before the EXECUTE IMMEDIATE statement 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

Usage 

The EXECUTE statement extends the range of statements that can be executed from within procedures and triggers. It lets you execute dynamically-prepared statements, such as statements that are constructed using the parameters passed in to a procedure.

Literal strings in the statement must be enclosed in single quotes, and 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 and triggers.

Permissions 

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.

Side effects 

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

For more information about using the EXECUTE IMMEDIATE statement in procedures, see Using the EXECUTE IMMEDIATE statement in procedures.

See also 

CREATE PROCEDURE statement

BEGIN statement

EXECUTE statement [ESQL]

Standards and compatibility 

• SQL/92    Intermediate-level feature.

• SQL/99    SQL/foundation feature outside of core SQL.

• Sybase    Supported in Open Client/Open Server.

Examples 

The following procedure creates a table, where the table name is supplied as a parameter to the procedure. The EXECUTE IMMEDIATE statement must all be on a single line.

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

To call the procedure and create a table called mytable:

CALL CreateTableProc( 'mytable' )

For an example of EXECUTE IMMEDIATE with a query that returns a result set, see Using the EXECUTE IMMEDIATE statement in procedures.