Источник:
http://sumitsaxfactor.wordpress.com/...quence-in-sql/
==============
In my last article, I explained how to
Generate RecIds in SQL Server.
In this article, I will provide a SQL Job to automatically generate next number sequence if a field is getting populated using Number sequences. I created this job recently for InventTransId.
So here is the job that will help you generate next number sequence in SQL. I have tried to put in as many comments as possible:
PHP код:
DECLARE @FORMAT AS NVARCHAR(20), @TMPFORMAT1 AS NVARCHAR(20), @TMPFORMAT2 AS NVARCHAR(20), @NEXTREC AS INT
DECLARE @FINALID AS NVARCHAR(20)
–FETCH THE NUMBERSEQUENCE FORMAT AND VALUES FOR INVENTTRANSID (Lot ID)
–NOTE THAT 582 is the Extended Type Id for InventTransId
SELECT @FORMAT =FORMAT, @NEXTREC = NEXTREC FROM NUMBERSEQUENCETABLE
WITH(UPDLOCK, HOLDLOCK)
JOIN NUMBERSEQUENCEREFERENCE ON NUMBERSEQUENCEREFERENCE.NUMBERSEQUENCE = NUMBERSEQUENCETABLE.NUMBERSEQUENCE
AND NUMBERSEQUENCEREFERENCE.DATATYPEID = 582 AND NUMBERSEQUENCETABLE.DATAAREAID = ‘CEU’
–GET THE # CHARACTERS IN ONE VARIABLE AND FIXED CHARACTERS (IF ANY) IN ANOTHER
–EX: ######_068 WILL BE BROKEN INTO "######" AND "_068"
SET @TMPFORMAT1=SUBSTRING(@FORMAT, 1, LEN(@FORMAT)-CHARINDEX(‘#’,REVERSE(@FORMAT))+1)
SET @TMPFORMAT2=SUBSTRING(@FORMAT, LEN(@TMPFORMAT1)+1, LEN(@FORMAT)-LEN(@TMPFORMAT1))
–NOW REPLACE # WITH REQUIRED ZEROS AND THE NEXT RECORD NUMBER THEN APPEND THE FIXED CHARACTERS AND DISPLAY OUTPUT
SET @FINALID = (REPLICATE(’0′, LEN(@TMPFORMAT1)-LEN(CAST(@NEXTREC AS NVARCHAR))) + CAST(@NEXTREC AS NVARCHAR)) + @TMPFORMAT2
SELECT @FINALID as INVENTTRANSID
Источник:
http://sumitsaxfactor.wordpress.com/...quence-in-sql/