Sharing A Solution To Generate Billcode

Introduction

In this article, you will learn a solution to generate billcode which is based on MS SQLSERVER .

Background

Many of systems we use will generate plenty of billcodes everyday , and most of them include date(datetime) or a prefix to show their signification , such as specifying a billcode belongs to a special day or a special type . 

Based on this scenario , I will share my idea and solution.

Let's start !

Preparation

First of all , we need a universal rule of the billcode so that we can generate through the rule .

Here is the rule summary with my experiences

  1. [prefix][date](optional)[number]  

At this Rule , the billcode combine with three parts . Following table explains all of them .

Part of RuleRequiredDescription
prefixtruespecify different types of the billcodes , such as MM , 1 , 1A . etc .
datefalsewhen using this part , the value must be the date , such as 20171015 , 20170909  . etc .
numbertruethe serial number , such as 0001 , 8900 . etc .

MM201710150001 and MM0000001 are sample billcodes which are based on above rule .

Storing the three parts into a table that we can make the settings more easier to maintain .

Creating a table named BillCodeSetting

  1. CREATE TABLE [dbo].[BillCodeSetting](  
  2.     [TableName] [varchar](255) NOT NULL,  
  3.     [Prefix] [varchar](20) NOT NULL,  
  4.     [NumberLength] [tinyint] NOT NULL CONSTRAINT [DF_BillCodeSetting_NumLen]  DEFAULT ((4)),  
  5.     [HasDate] [tinyint] NOT NULL,  
  6.  CONSTRAINT [PK_BillCodeSetting] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [TableName] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  10. ON [PRIMARY]  

For the meaning of each field , you can follow the below table .

FiledsDescription
TableNamename of table
Prefixbillcode's prefix
NumberLengthlength of serial number
HasDatewhether billcode contains current data

Note

There is one more field named TableName we don't mention , it specifies which table uses this setting . What I want to do is to prevent the following scenario .

A prefix is suitable for many types of billcodes , but we also expect each type of billcode can increase no gap !

Take a look on the below two sequences .

1,2,3,4,5.....

1,3,6,8,10....

The first sequence is what we need !

The next thing we need to do is to get the max serial number for each rule so that it can return a new billcode .

Create a table named BillCode to store the max serial number for each rule(table) .

  1. CREATE TABLE [dbo].[BillCode](  
  2.     [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
  3.     [TableName] [varchar](255) NOT NULL,  
  4.     [BillDate] [dateNULL,  
  5.     [BillNumber] [bigintNOT NULL,  
  6.  CONSTRAINT [PK_XT_BillMaxNum] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [Id] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  10. ON [PRIMARY]  

For the meaning of each field , you can follow the below table .

FieldsDescription
Ididentification
TableNamename of table , the same as BillCodeSetting's TableName
BillDatedate of current date , everyday will add a record
BillNumbermax serial number of everyday

Note

When billcode contains the date , this table will add a new record everyday if you use this.

OK , we have finished the previous works ! Turning to generate next time .

How To Generate ?

We will create a storedprocedure to generate the billcode here !

  1. CREATE PROCEDURE [dbo].[USP_GetBillCode]   
  2.     @TableName VARCHAR(255),  
  3.     @BillDate DATE = NULL,  
  4.     @BillCode VARCHAR(20) OUT  
  5. AS  
  6. BEGIN  
  7.     DECLARE @Prefix AS VARCHAR(20)  
  8.     DECLARE @NumLen AS INT  
  9.     DECLARE @Number AS BIGINT  
  10.     DECLARE @HasDate AS TINYINT  
  11.       
  12.     SET NOCOUNT ON  
  13.       
  14.     -- 1. Getting the settings   
  15.     SELECT TOP 1  @Prefix = [Prefix] , @NumLen = [NumberLength], @HasDate=[HasDate]  
  16.     FROM [dbo].[BillCodeSetting] WHERE [TableName] = @TableName  
  17.       
  18.     -- 2. Updating the max billcode number  
  19.     IF @HasDate=0  
  20.         UPDATE [dbo].[BillCode]   
  21. SET [BillNumber]=[BillNumber]+1,@Number=[BillNumber]+1   
  22.         WHERE [TableName] = @TableName   
  23.     ELSE  
  24.         UPDATE [dbo].[BillCode]    
  25. SET[BillNumber]=[BillNumber]+1,@Number=[BillNumber]+1   
  26.         WHERE [TableName] = @TableName And [BillDate] = @BillDate  
  27.       
  28.     -- 3. Inserting a new record to BillCode if not exists before  
  29.     IF @@ROWCOUNT = 0  
  30.         BEGIN  
  31.         INSERT INTO [dbo].[BillCode]([TableName],[BillDate],[BillNumber])   
  32.         VALUES (@TableName,@BillDate,1)  
  33.         SET @Number=1  
  34.         END  
  35.       
  36.     -- 4. Get a new number  
  37.     SET @Number = @Number + 1000000000000000000  
  38.   
  39.     -- 5. Generate a new billcode  
  40.     IF @HasDate=0  
  41.         SELECT @BillCode=@Prefix + RIGHT(CAST((@Number) AS VARCHAR(20)),@NumLen)  
  42.     ELSE  
  43.         SELECT @BillCode=@Prefix + REPLACE(CONVERT(varchar(10),@BillDate,120),'-','') + RIGHT(CAST((@Number) AS VARCHAR(20)),@NumLen)  
  44.           
  45.     SET NOCOUNT OFF  
  46. END  

The following code demonstrates that how to execute this storedprocedure via T-SQL

  1. DECLARE @return_value INT,  
  2.         @BillCode VARCHAR(20),  
  3.         @TmpDate DATE  
  4. SELECT @TmpDate = GETDATE()  
  5.   
  6. EXEC    @return_value = [dbo].[USP_GetBillCode]  
  7.     @TableName = N'Demo',  
  8.     @BillDate = @TmpDate,  
  9.     @BillCode = @BillCode OUTPUT  
  10. SELECT  @BillCode AS N'@BillCode'  
  11. SELECT  'Return Value' = @return_value  

Before running this code , let's take a look on BillCodeSettings table , and we will use the first record(rule) to show the result .

SQL Server

After running this code in my compute , the result is Demo201710140019.

SQL Server

Turning to the BillCode table , you will find that the max serial number of Demo is 19.

SQL Server

By the way , using ADO.NET or ORM frameworks will be very easy when we call this procedure in our programs .

Summary

In this article , I share a simple solution to generate billcode based on MS SQLSERVER . You can make some extensions to support your own systems . Hope this solution can help you !


Similar Articles