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 = ON) ON [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] [date] NULL,
  5. [BillNumber] [bigint] NOT 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 = ON) ON [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
  12. SET NOCOUNT ON
  14. -- 1. Getting the settings
  15. SELECT TOP 1 @Prefix = [Prefix] , @NumLen = [NumberLength], @HasDate=[HasDate]
  16. FROM [dbo].[BillCodeSetting] WHERE [TableName] = @TableName
  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
  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
  36. -- 4. Get a new number
  37. SET @Number = @Number + 1000000000000000000
  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)
  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()
  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 !