Generate Next Numbers with SQL Server
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.
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|
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
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
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
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.
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.