Ahh, excellent! I'll try this now.

So...this type of query would negate the need to use the
auto-increment trigger...unless it encountered a null value, of course.

Very cool, thanks a ton. Great book too, making my way through it slowly.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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