Automatically add serial numbers via SQL

Automatically add serial numbers via SQL

The project often have some document serial number or process serial number is to create a new record after the automatic generation of a number, such as a new process automatically based on the current date automatically add the process flow number, the following describes two different types of serial numbers automatically generated by SQL method.
Serial number format: www.2cto.com

First (numeric type): Date + serial number, such as "201104190001"--"201104190002";

Second (text type): prefix + date + serial number, such as "WT201104190001"--"WT201104190002".

In either case, the serial number of the generation principle is the same, the production of the serial number will need to be based on the current date, the new record of the serial number for all records under the current date of the maximum number of the line plus 1.
Www.2cto.com
One, add a table to the database: Testno

Column name Data type description

ID uniqueidentifier primary Key

No bigint serial Number field, but a pure digital serial number, this is the first case of this example, the serial number can be a pure number

DT datetime Adds the current time of a record, believing that each new record will record the current time of the operation.

Charno varchar (50) Serial Number field, but a serial number that can have characters, this is the second case of this example.

Second, the SQL code
------
--The automatic serial number is a purely numeric case, such as 201104190005
------
Declare
@x int,
@y Char (8)
Select @x= COUNT (NO) from Testno where CAST (DT as Date) =cast (GETDATE () as date)
if (@x=0)
Begin
Select @y=convert (char (8), GETDATE (), 112)
Insert Testno (NO,DT) VALUES (
@y+ ' 0001 '
, GETDATE ())
End
Else
Insert Testno (NO,DT) VALUES (
(select MAX (No) +1 from Testno where CAST (DT as Date) =cast (GETDATE () as date))
, GETDATE ())
SELECT * FROM Testno

------
--The case of automatic serial number is a character, such as WT201104190005
------
Declare
@x1 int,
@y1 Char (8)
Select @x1 = COUNT (Charno) from Testno where CAST (DT as Date) =cast (GETDATE () as date)
if (@x1 =0)
Begin
Select @y1 =convert (char (8), GETDATE (), 112)
Insert Testno (CHARNO,DT) VALUES (
' WT ' [email protected]+ ' 0001 '
, GETDATE ())
End
Else
Begin
Insert Testno (CHARNO,DT) VALUES (
' WT ' +cast ((select MAX (CAST (SUBSTRING (charno,3,12) as bigint) +1 from Testno where cast (DT as Date) =cast (GETDATE () as Date ) as varchar (50))
, GETDATE ())
End
SELECT * FROM Testno

Automatically add serial numbers via SQL