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

Mike,

This should work:

*************
DROP FUNCTION NEXTSEQ;
Create Function NEXTSEQ (RESET NUMERIC(04, 0) )
Returns CHAR(07)
Language SQL
Modifies SQL DATA
No External Action
Not Fenced
Not Deterministic
BEGIN
Declare NumVal Numeric(04, 0);
Declare RetVal Char(07);
Select NextKey INTO NumVal From CONSTANT;
Update CONSTANT Set NextKey = NextKey + 1;
Set RetVal =
CONCAT(CONCAT(Substr(year(Current_Date), 3), '-') , digits(NumVal) )
;
Return(RetVal);
END
*************



Best Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries