Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Generate Next Numbers with SQL Server

By Dinesh Asanka,

Introduction

Generating next numbers in SQLServer should not be a problem. But problem arises when customer ask for different types of next numbers where you cannot generate directly from SQLServer. This brief article describes how you would tackle this problem in different scenarios.

Number Generating Requirements
I was in a team of developing Invoicing System for a major photo developer Sri Lanka which has Orders, Invoices and Receipts among others. When we carried out a system study we found that their number generating is quite different to other clients, which we have implemented. Normally others would be happy if it is a general increment which is unique. In SQLServer there is a property of Increment which we were using happily for some times. But for this particular customer things were not easy.

For all numbers there should be a prefix which attached to it. It is “O”, “I” and “R” for Orders, Invoices Receipts respectively. Invoice number should be reset in every month while receipt number reset in every year. Invoice number consists of year and month while receipt number must contain year for its number.
Order number sequence is depending on the channel wise. This client has several channels. Channel 'A' order number has different sequence to that of channel 'B'.
There are some other source documents such as delivery orders, advance slips where their sequence number method has not implemented at the time of the discussion. But they said it will be one of the above methods.

These are their main requirement to the number generating. Thing are not easy as it looks.

Implementation

As the fully implementation is not completed we had to design in advance. We adopted the following methods of doing it. Main steps are storing last number, reading last number and updating it.

Storing Last Number

Followings are the file structures, which are used to keep the last number. Main Seq_no table was introduced to keep the Sequence

CREATE TABLE [dbo].[SEQ_NO] (

[TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Prefix] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LastNumber] [int] NULL ,

[Length] [smallint] NULL ,

[Year] [bit] NULL ,

[Month] [bit] NULL ,

[LastYear] [smallint] NULL ,

[LastMonth] [smallint] NULL ,

[TableName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 

) ON [PRIMARY]

GO

Sample Data for the SEQ_NO
Type Prefix Last Number Length Year Month Last Year Last Month Different Table TableName

INV

I 984 4 1 1 2004 2    
ORD   0 8 1 0 2004   1 SEQ_CHAN
RCT R 125 10 0 0        


2. Table for keep channel wise sequence numbers

CREATE TABLE [dbo].[SEQ_CHAN] (
[Code] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastNumber] [int] NULL 
) ON [PRIMARY]
GO

Sample Data for the SEQ_CHAN
 

Code LastNumber
A 9534
B 835
Reading and updating Last Number

I have used single stored procedure for reading and updating the last number. This is due to the fact that in multi user environment, there can be a case where two users get same number for the same record type. To avoid that at the point of generating the next number, number will be updated to the tables.

The stored procedure (sp_Get_Next_Number) has three parameters. First parameter is the type i.e. INV or ORD or RCT. The second parameter is date. Normally date is current system date. However, in the cases of generating previous document, date has to be parameterized keeping the system date as the default. Last parameter is optional parameter for the second table code.

/* Purpose :Get Last Number 

Author : PPG Dinesh Asanka

Create Date : 2002-01-23


Version Date Modification 

1.01 2002-02-28 Change to Get Last Number from Channel wise table

*/

CREATE PROCEDURE [dbo].[sp_Get_Next_Number]

@TypeID as varchar(5),@TranDate as varchar(20) = "",@secondType as varchar(5) = ""

AS

DECLARE @Prefix VARCHAR(1),

@LastNumber int,

@Length smallint,

@Year bit,

@Month bit,

@LastYear smallint,

@LastMonth smallint,

@DiffrentTable bit,

@TableName varchar(10),

@TrnYear varchar(2),

@TrnMonth varchar(2),

@char_Next_Number as varchar(25),

@SQL as varchar(120),

@charLastNumber as varchar(15)



If @TranDate = "" 

Select @TranDate = GetDate()



if exists (Select * From SEQ_NO Where TYPE = @TypeID)

begin

Select @prefix = Prefix,

@LastNumber = LastNumber,

@Length = Length,

@Year = [Year],

@Month = [Month],

@LastYear = [LastYear],

@LastMonth = [LastMonth],

@DiffrentTable = [DiffrentTable],

@TableName = [TableName]

From SEQ_NO Where TYPE = @TypeID



if (@DiffrentTable = 1) 

Select @LastNumber = LastNumber From SEQ_CHAN Where CODE = @secondType



Select @LastNumber = @LastNumber + 1

-- To Fill 0 for prefix

Select @charLastNumber = RTRIM(REPLICATE("0",@length - len(@LastNumber)) + cast(@LastNumber as varchar(10)))



Select @TrnYear = RIGHT(DATEPART(yyyy, @TranDate) ,2)

Select @TrnMonth = Month( @TranDate)

If Len(@TrnMonth) = 1 Select @TrnMonth = '0' + @TrnMonth



if @Year = 1 AND @Month = 1 -- Next Number is change for Every Month

if @LastMonth = Month(@TranDate) AND @LastYear = RIGHT(DATEPART(yy, @TranDate) ,2)

Select @char_Next_Number = @TrnYear + @TrnMonth + @charLastNumber

else

begin

Select @char_Next_Number = @TrnYear + @TrnMonth + REPLICATE ("0" ,@length -1) + '1'

Select @LastNumber = 1

Select @LastMonth = @TrnMonth

Select @LastYear = @TrnYear

end

else

if @Year = 1 -- Next Number is changing for Every year

if @LastYear = RIGHT(DATEPART(yy, @TranDate) ,2)

Select @char_Next_Number = @TrnYear + @charLastNumber

else

begin 

Select @LastYear = @LastYear

Select @LastNumber = 1

Select @char_Next_Number = @TrnYear + REPLICATE ("0" ,@length -1) + '1'

end

else

Select @char_Next_Number = @charLastNumber





Select RTRIM(LTRIM(ISNULL(@prefix,"") + @char_Next_Number))



-- Update Tables for next Numbers

if (@DiffrentTable = 1) 

begin



Select @sql = "Update " + @Tablename + " Set LastNumber =" 

+ Cast(@LastNumber as varchar(10)) + 

" Where CODE ='" + @secondType + "'"

execute (@sql)

end

Update SEQ_NO Set LastNumber = @LastNumber,

LastYear = @LastYear,

LastMonth = @LastMonth

Where TYPE = @TypeID

end

else

Select "Invalid Type"


Examples

Following results will be returned for the above data set

sp_Get_Next_Number 'INV' = I04020985
sp_Get_Next_Number 'RCT' = R0000000126
sp_Get_Next_Number 'ORD','','A' = 0400009535

Limitations
Problem arises when user trying to make ad hoc changes for the existing numbering system. Therefore, specifically we advised them not to do ad hoc changes to the numbering method.

Conclusion
Until now, we have not found any problems with regard to the above numbering system. Do you have any other way of doing these things? I warmly welcome any suggestions and improvements to the above numbering system.

Total article views: 14294 | Views in the last 30 days: 7
 
Related Articles
FORUM

Select just three octets from IP number

Select just three octets from IP number

FORUM

How to declare parameters inside "select"?

select asset.asset_name asset_name (varchar(100))?

FORUM

Difference between varchar(max) and varchar(8000)

Difference between varchar(max) and varchar(8000)

FORUM

VARCHAR(x) VS VARCHAR(MAX)

any downside to VARCHAR(MAX)?

FORUM

Regarding using VARCHAR(MAX)

Usage of VARCHAR(MAX)

Tags
miscellaneous    
programming    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones