ASA SQL User's Guide
  Using Procedures, Triggers, and Batches

Using the EXECUTE IMMEDIATE statement in procedures

The EXECUTE IMMEDIATE statement allows statements to be constructed inside procedures using a combination of literal strings (in quotes) and variables.

For example, the following procedure includes an EXECUTE IMMEDIATE statement that creates a table.

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

The EXECUTE IMMEDIATE statement can be used with queries that return result sets. For example:

CREATE PROCEDURE DynamicResult(
   IN Columns LONG VARCHAR,
   IN TableName CHAR(128),
   IN Restriction LONG VARCHAR DEFAULT NULL)
BEGIN
    DECLARE Command LONG VARCHAR;
    SET Command = 'SELECT ' || Columns || ' FROM ' || TableName;
    IF ISNULL( Restriction,'') <> '' THEN
         SET Command = Command || ' WHERE ' || Restriction;
    END IF;
    EXECUTE IMMEDIATE Command;
END

The following statement calls this procedure:

CALL DynamicResult(
   'table_id,table_name',
   'SYSTABLE',
   'table_id <= 10')

In ATOMIC compound statements, you cannot use an EXECUTE IMMEDIATE statement that causes a COMMIT, as COMMITs are not allowed in that context.

For more information about the EXECUTE IMMEDIATE statement, see EXECUTE IMMEDIATE statement [SP].