March 25, 2012 at 6:00 am
Hi,
I have to generate a sequence in the following manner
3 Characters Group Code +'/' + 3 Characters Doc Type Code + '/' + 2 digts of current year + 4 digits sequence padded with 0's
Example
CBG/POL/12/0001
In my stored procedure I am passing Group Code and Doc Type Code and the procedure should check in where clause if any number exists with the passed Group Code and Doc Type Code and current year 2 digits and if null returns it should generate CBG/POL/12/0001 otherwise CBG/POL/12/0002 or the next sequence..
how to achive this.
March 25, 2012 at 6:43 am
i have solved my own question like this.
CREATE PROCEDURE [dbo].[Proc_GenerateDocNo]
@BGroup varchar(3),
@DocType varchar(3),
@DocNumber varchar(15) OUTPUT
AS
BEGIN
DECLARE @curr_Seq varchar(50)
SELECT @curr_Seq = ISNULL(MAX(right(All_Documents.DocNo,4)),'0000')+1 FROM All_Documents
WHERE LEFT(All_Documents.DocNo,3) = @BGroup AND SUBSTRING(LEFT(All_Documents.DocNo,7),5,3) = @DocType
AND SUBSTRING(LEFT(All_Documents.DocNo,10),9,2) = RIGHT(DATEPART(YY,GETDATE()),2)
SELECT @DocNumber = @BGroup + '/'+@DocType+'/'+RIGHT(DATEPART(YY,GETDATE()),2)+'/'+RIGHT('0000' + + CAST(@curr_Seq AS varchar(4)),4)
March 25, 2012 at 9:54 am
Now all you have to do is solve the problem of concurrent runs getting the same number because your code does allow that to happen. It may be rare, but it can happen especially since there's nothing in that code to lock down the "reservation" of the sequence number within the same code that determines what it should be. AND, you have to be VERY careful when you do so or you'll create the world's capitol for deadlocks.
I have a sproc that will do simple numeric sequences that can very easily be modified for what you want to do at the following link. It's well documented and you shouldn't have a problem with the conversion. The key is that you have to both update the sequence table and get the new sequence number in the same "query" to prevent duplicates due to concurrent runs, which is what the code does quite nicely. Here's the link.
http://qa.sqlservercentral.com/Forums/FindPost861178.aspx
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply