Sybase 12.4.2 manual Using the Execute Immediate statement in procedures, 264

Using the EXECUTE IMMEDIATE statement in procedures

END

MESSAGE ’Outer compound statement’;

END

The following statement executes the InnerProc procedure:

CALL InnerProc();

The message window of the server then displays the following:

Hello from InnerProc

Column not found handling

Outer compound statement

When the SIGNAL statement that causes the error is encountered, control passes to the exception handler for the compound statement, and the Column not found handling message is printed. Control then passes back to the outer compound statement and the Outer compound statement message is printed.

If an error other than column not found is encountered in the inner compound statement, the exception handler executes the RESIGNAL statement. The RESIGNAL statement passes control directly back to the calling environment, and the remainder of the outer compound statement is not executed.

Using the EXECUTE IMMEDIATE statement in procedures

The EXECUTE IMMEDIATE statement allows statements to be built up 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

264