---------- Original Message ----------- From: "un_spoken" <brucedickin...@wp.pl> > 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
