> Mitch,
>
>> Is there a 100% reliable way of returning the primary key value of a
>> new row added by a stored procedure?
>>
>> My procedure adds a row (which gets an auto generated PK ID) and then
>> needs the PK ID to do further processing. The temporary method that I
>> am using involves using a datestamp variable and inputting this during
>> row insert. Once the row is inserted I select for the row with that
>> timestamp to find the ID.
>>
>> This seems inelegant and the row datestamp is not guaranteed to be
>> unique. Is there a better way?
>
> Indeed, there is a better way.
>
> Adjust your trigger to only assign a value if the ID columns is empty,
> eg:
>
> IF (NEW.ID IS NULL OR NEW.ID = 0)
> THEN ...
>
> And modify your procedure so that it grabs a new value from the generator
> first and uses that in the insert. You can safely return that new value to
> your
> client.
>
> This way, you still have your trigger for SQL that doesn't use the procedure
> AND you have a way to return the new value from your procedure.
>
>
> In Firebird 2.0, you could also check the RETURNING VALUES clause
> from an INSERT INTO and get the new value in your procedure and
> return it. Have a look at the Release Notes or new SQL extensions.
>
> Martijn Tonies
> Database Workbench - development tool for Firebird and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
>

RETURNING VALUES is valid for Firebird 2.1.

But no need for it in Stored Procedures.
Just use a selectable stored proc that return one single row with the
field id.
Then you can use a select statement for insert a row.

Example:

select id from InsertProcAdress('MyPrename', 'MyName' );