At 12:34 PM 21-03-01 -0600, you wrote:

>I am trying to write a simple stored proc that returns the results of
>running Gen_Id. I am trying to use this code:
>
> Create Procedure GeneratePrimaryKey
> Returns ("PK" Integer )
> As Begin
> "PK" = Gen_Id( PrimaryKeyGenerator, 1 );
> End !!
> Set Term ; !!
>
>I execute this code using both of these:
>
> Execute Procedure GeneratePrimaryKey

would return a parameter

> Select PK From GeneratePrimaryKey

would return a dataset *only* if you include a SUSPEND in your SP, otherwise an empty dataset.

>However, though the PrimarykeyGenerator value is being incremented with
>both approaches, neither of these approaches are actually returning the
>generated value. I get back a table with one column and no data.

1. What are you using to read the data returned? It does matter.
2. What version of IB are you using? if it is v. 6 then Gen_ID() will not return an integer but a NUMERIC(18,0).
2. Your P/L syntax is pretty unconventional. AFAIK, the double-quote convention is for object identifier names, not program variables or procedure arguments. Try rewriting it according to the convention, i.e.

Create Procedure GeneratePrimaryKey
Returns (PK Integer )
As Begin
PK = Gen_Id( PrimaryKeyGenerator, 1 );
SUSPEND;
End

or, if it is v.6,

Create Procedure GeneratePrimaryKey
Returns (PK Numeric(18,0) )
As Begin
PK = Gen_Id( PrimaryKeyGenerator, 1 );
SUSPEND;
End

btw, don't write a procedure that performs its own increment on Gen_ID(GeneratorName,0) as suggested by somebody. This is unsafe and totally defeats the point of using a generator which is (a) to ensure that PK values are unique and (b) to keep the appointment of numbers for primary key assignments outside transaction control.

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________