Sybase 12.4.2 User Manual

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.

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