Thanks Helen. Gee, I didn't know that I wrote 'monumental' procs...
just the normal data processing stuff. I'll try to heed your advice in
the future.

Monumental.....

Anyway when I do an execute statement call how do I deal with single
quotes within the statement?:

CAST('' AS VARCHAR(12)) AS MERGE_VALUE,
CAST('N' AS CHAR(1)) AS MERGE_DELETE

Is there a particular escape character that needs to be used when
quoting 'quote' characters?

Thanks again.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
> This is "as designed". PSQL is not a scripting tool for designing
output structures dynamically at run-time, it's a mechanism for
processing data. Sets on which the code operates are structurally
static and you can't manipulate them into a different structure.
Field lists and sorting criteria (ORDER BY GROUP BY criteria) are static.
>
> If you are using Fb 1.5 or higher, you can generate a run-time
statement string inside your SP using EXECUTE STATEMENT. This might
help you achieve what you want. It is described in detail in the
v.1.5 release notes.
>
> As a further urgent piece of advice, it is very unwise to write
stored procedures that mix execution of data state changing operations
with constructing output sets. Define your data state changing
operations in executable SPs and your set-returning operations in
selectable SPs. Not only are you returning a set that's inconsistent
with the state of the database, but any "refresh" operation will
re-execute all of the DML code.
>
> You should also try to avoid writing "monumental" SPs. Try to design
them modularly and call them from within the "base" SP using EXECUTE
[args..]...[returning_values]. Apart from the structural ugliness
that makes monumental SPs hard to troubleshoot, there are limits on
the total number of bytes (48K) that can be stored in the BLR (the
executable code that is created by the engine at compile time for
execution at run-time). At run-time the total length of the BLR is
augmented by the total sizes of actual data being carried around in
parameters, meaning it's not too hard to tip over the maximum size of
an SQL statement (64K).
>
> In complex executable SP code you should also be building in
exception handling.
>
> >Is the only way to do this to use the result of a UDF??
>
> God forbid.
>
> ./heLen
>