SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE cim_getnext_seqno
@plntno CHAR(4),
@function CHAR(20),
@sequence CHAR(20) OUTPUT
DECLARE
@alert_message VARCHAR(250),
@empno CHAR(10)
DECLARE
@nmstat_prefix CHAR(10),
@nmstat_minseqno int,
@nmstat_maxseqno int,
@nmstat_nxtseqno int,
@nmstat_postfix CHAR(10),
@nmstat_error CHAR(10)
Set NoCount On
BEGIN TRAN Getnext_Seqno
select @empno = '99901'
select @nmstat_prefix = PREFIX,@nmstat_minseqno = MINSEQNO,@nmstat_maxseqno = MAXSEQNO,
@nmstat_nxtseqno = NXTSEQNO,@nmstat_postfix = POSTFIX
from NMSTAT (TABLOCKX HOLDLOCK) where PLNTNO = @plntno and FUNC = @function
if(right(@nmstat_prefix,1) != ' ' and @nmstat_prefix IS NOT NULL)
select @sequence = rtrim(@nmstat_prefix) + ltrim(CONVERT(VARCHAR(10),@nmstat_nxtseqno))
else
select @sequence = ltrim(CONVERT(VARCHAR(10),@nmstat_nxtseqno))
if(right(@nmstat_postfix,1) != ' ' and @nmstat_postfix IS NOT NULL)
select @sequence = rtrim(@sequence) + rtrim(@nmstat_postfix)
select @nmstat_nxtseqno = @nmstat_nxtseqno + 1
if (@nmstat_nxtseqno > @nmstat_maxseqno)
select @nmstat_nxtseqno = @nmstat_minseqno
if (@nmstat_nxtseqno < @nmstat_minseqno)
select @nmstat_nxtseqno = @nmstat_minseqno
update NMSTAT set NXTSEQNO = @nmstat_nxtseqno,UPDTTMS = getdate()
where PLNTNO = @plntno and FUNC = @function
COMMIT TRAN Getnext_Seqno
RETURN 0
