Shulou(Shulou.com)05/31 Report--

This article is to share with you the content of a case where SQL server generates unique order numbers with high concurrency. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. scene reproduction

In an erp purchase, sale and storage system or other systems such as 0A, if multiple people generate the order number at the same time, it is easy for multiple people to get the same order number, which will cause irreparable losses to the company's business.

Second, how to avoid high concurrency when the order number is not unique

We can use the stored procedure and the data table to create a table and a stored procedure, which is responsible for generating the order number and the table is responsible for dealing with the uniqueness problem.

When the stored procedure generates an order number, first write the order number into the table, then display the result of the order number, and write the generated order number into the table. Why? Because our table has a primary key (primary key uniqueness)

Can be written in: when there is no same order number in the table, that is, write the generated order number in the table.

Cannot be written in: when the same order number exists in the table, that is, the generated order number cannot be written into the table, the order number cannot be obtained, thus ensuring that a unique order number is generated under high concurrency.

The process of generating a unique order number in the case of high concurrency

The following will explain the process of generating a unique order number with code and practice

Step 1: create a data table and set the order number field as the primary key (the key of the unique order number)

Step 2: create a stored procedure that generates a subscription number

The generated order number format is: custom prefix + year, month and day + suffix (001, 002, 003)

1. First create a stored procedure

Parameter is @ BILL_TYPE

CREATE PROCEDURE [dbo]. [GetBillNO] @ BILL_TYPE nvarchar (3)

two。 Generate year, month, day and suffix

Year, month and day is the current system time, and the initial value of the suffix is 0

Declare @ BILL_NO nvarchar (15); declare @ currentDate nvarchar (15); declare @ lastno INT = 0PosiSet @ currentDate = Convert (varchar (10), Getdate (), 112)

3. Query the form to get the order number of the form

Query the table, get the latest order number with the prefix related to the custom content and assign it to @ BILL_NO

Select @ BILL_NO = isnull (BILL_NO,0) FROM temp_bill WHERE SUBSTRING (BILL_NO,1,3) = BILL_TYPE and SUBSTRING (BILL_NO,4,8) = @ currentDate

4. Generate order number

If the order number obtained in the previous step is not empty, the newly generated order number is + 1 on the original order number.

Example: the order number obtained: QNB20200615015

The generated order number is: QNB20200615016

If the order number obtained in the previous step is empty, the newly generated order number is suffixed with 001

Example: generated order number: QNB20200615001

IF @ BILL_NO! =''begin SET @ lastno = str (right (@ BILL_NO, 3) + 1); set @ BILL_NO = @ BILL_TYPE+@currentDate+RIGHT (' 000'+CAST (@ lastno as varchar), 3); end;ELSE begin set @ BILL_NO = @ BILL_TYPE+@currentDate+CAST ('001' as varchar (20)); END

When doing this, SQL server does not have a LPAD function (not enough to automatically complete the specified number of digits), so I use string concatenation, and then use right to get 3 bits on the right to achieve my goal.

5. Insert the generated order number into the table

Insert table fails if the same order number exists in the table

Insert the table successfully if the same order number does not exist in the table

Insert into Temp_Bill (BILL_NO,BILL_TYPE) values (@ BILL_NO, @ BILL_TYPE)

6. Returns the unique order number

When the order is successfully inserted into the table, the unique order number can be returned (if the previous step is not successful, this step will not be run)

Select @ BILL_NO as BILL_ no. 4. Run result

1. First of all, there is no data in my table, so I will generate a prefix (I entered: UIE) + year, month and day (20200615) + 001 (the first data, so it is 001)

Namely: QNB20200615001

2 when I enter it for the second time, because there is data in the table, I will add 1 according to the suffix of the latest order number

Namely: QNB20200615002

V. Old rules

I hope this article can help you all.

If you have enough points, just download them directly. If you have no points, you can click on the picture. Anyway, the amount of code is not large.

If you are a student and have no points, you can talk to me in private and give you the source code for free.

Code download link: SQL server_getbillno.bak

Code screenshot:

Thank you for reading! This is the end of this article on "the case of SQL server generating unique order number with high concurrency". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.