Subject Re: [firebird-support] SP not returning a table
Author Helen Borrie
At 03:02 AM 25/04/2007, you wrote:
>I've finished a stored procedure and I need it to return mytable
>(that I modify in the stored procedure) so I just added a SELECT *
>from mytable as the last line of the stored procedure but I'm
>getting an error:
> SQL error code = -104.
>Token unknown - line 74, char 22.
> But a table can't be an output parameter of a SP, should I
> execute the SP and then show mytable doing the SELECT outside the SP?

How much do you know about selectable stored procedures in Firebird
PSQL? Do you understand that different database engines do
procedural SQL in different ways?

In SQL you don't "output tables" at all. You output SETS. In
Firebird PSQL you can create a set inside your SP and then, on the
client side, SELECT it as though it were a table.

create procedure mytable (input1 integer, {etc.})
returns (
mt1 integer,
mt2 varchar(20),
mt3 {etc.} )
for select field1, field2, field3 from atable
where ...
order by ...
into :mt1, :mt2, :mt3, {etc.}
{do whatever you need to the data};