I have following table

CREATE TABLE SYSVALS (
CLIENTID D_INTEGER NOT NULL,
ORDERSEQ D_INTEGER NOT NULL,
INVOICESEQ D_INTEGER NOT NULL);


ALTER TABLE SYSVALS ADD PRIMARY KEY (CLIENTID);

One record exists per customer. Customers want to choose their own order and invoice sequence.

So I was thinking a simple stored procedure to all my app to get the next record for the client specified. Below is my first attempt at storedproc, but it increments the value by 2.

CREATE PROCEDURE GETSYSVAL(
CLTID INTEGER)
RETURNS(
SEQVAL INTEGER)
AS
DECLARE VARIABLE iVAL1 INTEGER;
DECLARE VARIABLE iVAL2 INTEGER;
BEGIN
/* Procedure body */
SELECT ORDERSEQ FROM SYSVALS WHERE CLIENTID = :CLTID INTO iVAL1 ;
SEQVAL = iVAL1 + 1;
UPDATE SYSVALS SET ORDERSEQ = :SEQVAL WHERE CLIENTID = :CLTID AND ORDERSEQ = :iVAL1;
SELECT ORDERSEQ FROM SYSVALS WHERE CLIENTID = :CLTID INTO iVAL1 ;
IF (iVAL1 <> SEQVAL) THEN
SEQVAL = 0;
SUSPEND;
END;

Any ideas? IS there a better way to do this. Generators are used in the app, but only for system wide values. Have had 2 pilot users request the ability to have their own unique value. I dont want to have to multiple sequences and complicated code to manage clients and sequences.

Anyway, thanks for taking a look at this.

- Lou