At 06:01 AM 7/12/2004 +0000, you wrote:

>I'm rather new to Firebird and can't find a solid example in the
>Firebird Book on how to return an ID in a stored procedure.
>
>I simply want to fire an insert and then return the last inserted ID.
>
>When I fire this sproc, nothing is inserted and, obviously, no ID is
>returned:
>
>create procedure ins_group
>(
> group_name varchar(20),
> description varchar(100),
> rank int
>)
>returns
>(
> group_id_ int
>)
>as
>begin
> insert into
> user_group
> values
> (
> null,
> :group_name,
> :description,
> :rank
> );
>
> select GEN_ID(gen_group_id, 0) from RDB$DATABASE into :group_id_;
> suspend;
>end
> ^
>
>For example...I executed it w/ this snippet:
>
>execute procedure ins_group
>(
> 'Administrator',
> 'Root-level access to entire system',
> 1
>);

First of all, this is *not* a wise way to try to get the correct result
returned. At the point where you query for the latest value of the
generator, you have no way to know whether the value returned is the one
that was used in your INSERT statement. If you want to return exactly and
only the number that was used for your insert, you must get the number
*before* you run the insert statement and explicitly include it. There is
quite a bit about this in the book.

Next, you are misusing SUSPEND here. Take it out. Since you have the
book, look up the notes about EXIT and SUSPEND (all of the references from
the alphabetical index).

Write the SP this way instead:

recreate procedure ins_group
(
group_name varchar(20),
description varchar(100),
rank int
)
returns
(
group_id_ int
)
as
begin
group_id = gen_id(gen_group_id, 1);
insert into user_group
values (
:group_id,
:group_name,
:description,
:rank
);
end
^

>Like I said, I don't get an error, I just get zero output and zero
>rows affected (using IBOConsole front-end tool).

AFAIK, the rows affected value only returns the count of rows **affected**
by the statement, i.e. updated or deleted. An insert didn't do anything to
pre-existing data, so the statement didn't affect any rows.

But IBOConsole doesn't support EXECUTE PROCEDURE at all well. In fact, in
the version I have, it completely refuses to accept an EXECUTE PROCEDURE
statement. In any case, its query tool uses a dataset component and
doesn't surface return parameters. The version I have here supports just
SELECT statements and DDL.

Download IB_SQL and use its DSQL tool, or use isql. Both will show you the
return values of an executable SP. IB_SQL will also report rows affected,
where it is available.

./heLen