Re: Stored Procedure (or SQL) to get a value then update it to +1

Mike,

Instead of a Stored Procedure, make it a function. That way you can, for
example, do a simple table update. Something like:

UPDATE ANOTHER_TABLE SET new_Sequence = NEXTSEQ(0);
A simple skeleton for a function that does what you want would be:
********
DROP FUNCTION NEXTSEQ;

Create Function NEXTSEQ (RESET NUMERIC(04, 0) )
Returns Numeric(04, 0)
Language SQL
Modifies SQL DATA
BEGIN
Declare RetVal Numeric(04,
0);
Select NextKey INTO RetVal From
CONSTANT;
Update CONSTANT Set NextKey = NextKey + 1;
Return(RetVal);
END
********

If reset <> 0 you could update NextKey with the new value, for instance.
That is not defined in this example, but I just wanted to show to how to
code an entry parameter.
Also, you could make it return yy-#### instead of just a number. Define
RetVal as CHAR(07) and code a CONCAT() just before the Return statement.

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries