At 06:53 PM 25-05-02 +0000, you wrote:

>Is there a way to enumerate fields in the result set of a select
>statement in a procedure? I have a table with 20+ fields, and want to
>return them one by one:
>
>for select * from tablename do
> for each field in result_set do
> begin
> resultvariable=field.value;
> suspend;
> end
>
>alternatively,
>
>select * from tablename into :my_array
>for each field in my_array do
>begin
> resultvariable=field.value;
> suspend;
>end
>
>
>Is something like this possible?

Possible...well, yes, with a horrendously complicated SP you could do
it. This isn't an SQL-like requirement at all. SQL deals with row-based
sets. You would have to be prepared to cast the value in every column to a
single data type and size in order to produce output that matched the
declared output parameter; or, alternatively, declare an output set with
one argument for each column, then output rows containing nulls in all but
one column. You can't loop across the columns of a row that is presented
to the SP as output from a SELECT * FROM....INTO <varlist>; so it would be
a case of either selecting each column one by one, casting it to match the
output argument and calling suspend; or selecting a whole row into a
corresponding set of variables and then casting the value in each variable
to fit the output argument.

You would have no way of identifying which column was which in the
output; nor any linking data which would get you back to the source row
for a positioned update. You would have to handle cases where there were
nulls; and you would not be able to cope with blobs. It's not very appealing.

As for arrays - again, near impossible, and casting would be required,
since you can't mix data types in arrays.

I'm curious to know what application requirement would call for something
like this...

Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________