Hello Tim,

> How would I return a value from a Stored Procedure?
>
> Assume I have the following metatdata, how do I get the SP to return
> the Recordno to me? :
>
> CREATE GENERATOR GEN_1;
>
> CREATE TABLE TABLE1
> (
> RECORDNO INTEGER NOT NULL,
> FIELD1 VARCHAR(32),
> PRIMARY KEY (ERECORDNO)
> )
>
> CREATE TRIGGER INS_TABLE1
> AS
> BEGIN
> NEW.RECORDNO = GEN_ID(GEN_1, 1);
> END;
>
> CREATE PROCEDURE INSERTPROCEDURE
> (
> FIELD1 VARCHAR(32)
> )
> RETURNS
> RECORDNO INTEGER
> AS
> BEGIN
> INSERT INTO TABLE1
> (FIELDONE)
> VALUES
> :FIELD1;
> END

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;



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