Tim,

>>You can't do this in a reliable way.
>>
>>Do it differently. Change your trigger to:
>>
>>if ( (new.recordno is null) or (new.recordno = 0) )
>>then new.recordno = gen_id(gen_1, 1);
>>
>>Now, in your procedure do:
>>
>>recordno = gen_id(gen_1, 1);
>>insert into table1 (recordno, fieldone)
>>values :recordno, :field1;
>
>One more question :
>
>I presume I would get this value (in Delphi) by using something like
>
>SELECT RECORDNO FROM MYPROCEDURE(VALUE)

Not really. What you have now, is an "executable" stored procedure.

If you want to use SELECT as well, you need to create a "selectable"
stored procedure. Which basically is a procedure that has "suspend"
in it. The suspend causes the procedure execution to halt and return
the values to the output parameter. If the client (with it's SELECT
statement) does a "next" and fetches the next row of values, the procedure
will continue executing.

However, IMO, it's _very_ bad practice to do a "select from
myprocedure" that updates/inserts data. Only use select-able
stored procedures for procedures that simply return data without
modifying it.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com