Hi Tim,

> What I want to do is insert a record in one table, then insert a record in
> another table, using a primary key value from the first table.
>
> Both inserts are done in seperate Stored Procedures. Both inserts take
> place in the same transaction context.
>
> The aim is to keep a one -> many relationship in sync.
>
> How do I do it?
>
> Thanks in advance,
>
> Tim
>
>
> This is what I have at present :
>
> ALTER PROCEDURE "INSERT_SMSMSG"
> (
> DATETIMERECEIVED TIMESTAMP,
> SITENO INTEGER,
> MSGDATETIME TIMESTAMP,
> MSGCOUNT INTEGER,
> MSGDSUID INTEGER,
> MSGKPA INTEGER,
> MSGTC INTEGER,
> MSGSTR CHAR(160) CHARACTER SET ASCII
> )
> RETURNS
> (
> MSGNO INTEGER
> )
> AS
>
> DECLARE VARIABLE SMSMSGNO INTEGER;
> BEGIN
> MSGNO = GEN_ID(GEN_MESSAGENO, 1);
> INSERT INTO SMSMESSAGE
> (RECORDNO, DATETIMERECEIVED, SITENO, MSGDATETIME,MSGHCOUNT, MSGDSUID,
> MSGKPA, MSGTC, MESSAGESTR)
> VALUES
> (:MSGNO, :DATETIMERECEIVED, :SITENO, :MSGDATETIME, :MSGCOUNT,
> :MSGDSUID, :MSGKPA, :MSGTC, :MSGSTR);
> END
> ^

That's fine so far. Simply call the SP above in your second
procedure. The following should get you started.

...
DECLARE VARIABLE MSGNO INTEGER;
BEGIN
EXECUTE PROCEDURE INSERT_SMSMSG (...) RETURNING_VALUES :MSGNO;
// Do whatever you want with MSGNO now
END

Is this what you are looking for?

HTH,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com