Filter
Top Products
Using the EXECUTE IMMEDIATE statement in procedures
264
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