Subject Re: [firebird-support] Returning a value from a Stored Procedure
Author Martijn Tonies

>>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

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
My thoughts:
Database development questions? Check the forum!