A simple method for generating serial numbers

Recently, when handling tickets, the serial numbers of tickets need to be generated automatically. Many methods have been found, which are troublesome and difficult to control concurrency, the format of the ticket number must be: the ticket ID + year, month, and day + the serial number of the day (for example, no.20080808001 and no.20080808002). The preceding steps are well handled. The key is to handle the serial number of the day, A single order number is about to appear immediately after a user creates a single order. Therefore, he wants to create a simple method to generate a daily serial number. The method to generate a serial number is very simple, but there is no good concurrency processing, the ticket number used during creation is likely to be used by others first, so I will perform another check when saving the ticket. If the ticket number is no longer used, a new ticket number will be generated, after the ticket number is saved, it is returned to the user, which solves the problem of the ticket number generation.

The table is as follows:

Table Name (Bill)

Field name	Field meaning
ID	Identifier
No	Serial number
Title	Ticket title
Createtime	Ticket Creation Time

 

To make it simple and clear, there are so many fields, how can I automatically add 1 for no according to the daily serial number when I add a ticket?

You can customize a function in SQL, and then call this function when inserting data.

/**//*
**************************************
Function: generate the serial number of the current ticket
Parameter: @ pfx prefix, for example, a, AA, Ba
Result: for example, a080721001 and a080721002 are returned.

Author: vaiyanzi (Email: vaiyanzi@gmail.com)
**************************************
*/
Create Function DBO. fun_getno (@ pfx nvarchar (10 ))
Returns nvarchar (20)
Begin
Declare @ No nvarchar (20)
Set @ No = @ pfx --
Declare @ TNO int
Select @ TNO = right (isnull (max (NO), 0), 3) + 1001 from Bill where (convert (varchar (10), createtime, 120) = convert (varchar (10), getdate (), 120 ))
Set @ No = @ No + convert (varchar (10), getdate (), 12) + right (@ TNO, 3)
Return @ No
End

 

In this way, you can call this function when inserting data to generate the daily ticket serial number.