User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To enable dynamically constructed statements to be executed from within a procedure.

Syntax

     Syntax 1:    EXECUTE IMMEDIATE string-expression

     Syntax 2:    EXECUTE (string-expression)

For information about the Embedded SQL EXECUTE IMMEDIATE statement, see "EXECUTE statement".

Usage

Procedures, triggers, and batches

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, then that commit does take place.

See also

• CREATE PROCEDURE statement
• Compound statements

Description

The EXECUTE IMMEDIATE statement extends the range of statements that can be executed from within procedures and triggers. It allows dynamically prepared statements to be executed, 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.

Example

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 if you run this example.

     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 mytable:

     CALL CreateTableProc( 'mytable' )