---------- Original Message -----------
From: "un_spoken" <brucedickinson@...>

> I am pretty sure that is not possible. I've read all info I could get
> on the web and it clearly says that output variables /columns must be
> defined for stored procedure or execute block. But maybe I am wrong?
>
> Maybe it is somehow possible to do something like this?
>
> SET TERM ^ ;
> EXECUTE BLOCK
> AS
> DECLARE S varchar(255);
> BEGIN
> S = 'SELECT ' || DYNAMIC_COL1 || ' , ' || DYNAMIC_COL2 || ' FROM MY_TABLE';
> EXECUTE STATEMENT S;
> END^
>
> The thing is that I am trying to build a query that will return a
> different number of columns (with varying names) basing on the input

parameters.

>
> But I guess that is not possible?:(
>
> Thanks for your time.

------- End of Original Message -------

Unlike SQLServer, Firebird wants all selectable blocks (anonymous as above, or
named as stored procedures) to have well-defined resultset metadata. In
particular, it must be possible to prepare the statement, examine the metadata
to know what columns will be coming back, and then start fetching data.

Also, remember that in Firebird, you can't just "execute statement" at the end
of a stored procedure (or execute-block) to return a resultset; you must at the
very least do

for execute statement :f into :a, :b, :c, ..., :z do
suspend;

where a-z must be somehow related to the returns () clause of your block. (This
also means a stored procedure can only return exactly one resultset, as
declared in its header.)

Your options are:
a) if it's just a SELECT, build the SELECT at runtime, and run it (by the time
you prepare the statement, it's already clear to the server what columns you
want)
b) if there's more to it, dynamically build up the EXECUTE BLOCK as you did
above, then execute the whole thing as a statement (keeping in mind that you
need to build up a statement that looks like:

execute block (parameter names and types...) returns (column names and
types...) as
begin
for select ... into ... do suspend;
end

-Philip