sumitsaxfactor: Generating Next Number Sequence in SQL

[Blog bot]

Источник: 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/