--- In firebird-support@yahoogroups.com, Robert martin <rob@...>
wrote:

>
> Hi
>
> I am writing a stored procedure to replace some existing app code.
>
> As part of the procedure I get a list of tables that meet certain
> criterion. I then want to run an update SQL on each of these

tables. In

> my code I built the SQL on the fly as shown below.
>
> i.e. 'UPDATE ' + fTableName + ' SET .........
>
> Can I do this in a SP (I suspect not)? Are there any other

options, I

> cant hard code the list of tables as they will change.?
>

You can do it. Whether you should or not is the real question. The
EXECUTE STATEMENT command was added to PSQL in Firebird 1.5 which
allows you to attempt to execute whatever you can place in a varchar
variable. See release notes for details.

You must be aware of the limitations though:

* You open up a large security vulnerability. If I know you have a
stored procedure that executes an update of an arbitrary field in an
arbitrary table, you need to consider a malicious person passing in
tablenames and fieldnames that they are not meant to (including
system tables).

* Performance will suffer. In this case, it would be no worse than
your application logic. Normally, Firebird will cache the optimal
plan of the particular statement inside a SP. This is impossible if
it does not know the table and fields involved.

* Parameters won't work. This is a real pain for such an approach,
because you will need to cast everything and wrap things in quotes.
Timestamps may also have formatting woes depending on date formats.

Adam