How to supply a view, procedure or table name to a stored procedure?
You might have a stored procedure that does something with some table, but it is determined at run-time which table it should be. You can do this with EXECUTE STATEMENT. For example, we wish to supply the table name from which we wish to extract the values for the field ID of that table:
SET TERM !! ;
CREATE PROCEDURE p1 ( TableName VARCHAR(32))
RETURNS ( id integer )
DECLARE VARIABLE stmt VARCHAR(1000);
stmt = 'select id from '||:TableName;
for execute statement :stmt into :id
SET TERM ; !!
You can do similar things with views and stored procedures as well. The only thing that cannot be variable is the number and names of output parameters (fields).