At 01:51 AM 31/07/2005 +0000, you wrote:

>I have an SP as
>SET TERM ^^ ;
>CREATE PROCEDURE P_ADD_ENTITY (
> ENTITY_TYPE Char(1),
> ENTITY_COMMENTS BLOB,
> ENTITY_EMAIL VarChar(75),
> ENTITY_RECORDSTATE Integer)
> returns (
> ENTITY_ID Integer)
>AS..........
>
>A "Before Insert" Trigger uses a Generator value to insert ENTITY_ID.
>How do I return the ENTITY_ID once this procedure is run (the SP adds
>a new record)?

Doing it this way you can't.

Instead, write your TRIGGER so that it tests for null before getting a new
vaue from the generator:


CREATE TRIGGER...
AS
BEGIN
IF (NEW.ENTITY_ID IS NULL) THEN
NEW.ENTITY_ID = GEN_ID(MYGENERATOR, 1);
END

And, in your SP:

AS
BEGIN
...........
ENTITY_ID = GEN_ID(MYGENERATOR,1);
INSERT INTO MYTABLE (
ENTITY_ID,
ENTITY_TYPE,
ENTITY_COMMENTS,
ENTITY_EMAIL,
ENTITY_RECORDSTATE)
VALUES (:ENTITY_ID, :ENTITY_TYPE, :ENTITY_COMMENTS,
:ENTITY_EMAIL, :ENTITY_RECORDSTATE);
..........
END

Just be sure that you use EXECUTE PROCEDURE to call this SP. To read the
return value, your application code needs a way to read the XSQLVAR that is
returned after the SP completes. How you do that depends entirely on the
data access interface you are using. For example, in Delphi, using
IBObjects, one would read FieldByName('ENTITY_ID').AsInteger; while other
Delphi interfaces would define a TParam of type ptOutput and read that from
the Params structure after execution.

Also realise that the value returned for ENTITY_ID is not a committed
value. It will not be visible to any other transactions until the
transaction in which EXECUTE PROCEDURE was called is successfully committed.

./heLen