Generate Complex Next Numbers with SQL Server

Generate Next Numbers with SQL Server

Introduction

Generating next numbers in SQLServer should not be a problem. But problems arise when a customer asks for different types of next numbers that you cannot generate directly from SQL Server. This brief article describes how you would tackle this problem in different scenarios.

Number Generating Requirements

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

For all numbers, there should be a prefix attached to it. The prefix is .O., .I., and .R. for Orders, Invoices, and Receipts respectively. The invoice number should be reset every month and the receipt number reset every year. The invoice number consists of year and month; the receipt number must contain the year for its number.

The order number sequence depends on the channel wise. This client has several channels. Channel ‘A’ order number has a different sequence than that of channel ‘B’.

There are some other source documents, such as delivery orders and advance slips; their sequence number method has not been implemented at the time of the discussion. But they said it will be one of the above methods.

These are their main requirements to the number generating. Things are not easy as it looks.

Implementation

Because the full implementation is not complete, we had to design in advance. We adopted the following methods of doing it.

The main steps are storing the last number, reading the last number, and updating it.

Storing the Last Number

Following are the file structures that are used to keep the last number.

A 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        

Table for keeping 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 the Last Number

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

The stored procedure (sp_Get_Next_Number) has three parameters. The first parameter is the type i.e. INV or ORD or RCT. The second parameter is tje date. Normally, the date is the current system date. However, in the cases of generating the previous document, the date has to be parameterized, keeping the system date as the default. The last parameter is an 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"

GO

Examples

The 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

Problems arise when the user tries to make ad hoc changes for the existing numbering system. Therefore, we specifically 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.

Dinesh Asanka

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read