Copy codeThe Code is as follows:
Create table [order]
(
Code varchar (50 ),
Createtime datetime
)
-- Apply usp_ordernumbergenerate (@ prefix = 'prc100701 ')
-- Input prefix category + document code + 6-digit date
-- Obtain the maximum sequential number of such types of documents on the current day (daily return is required)
-- There are two methods to determine the table: one is to judge the table based on the input six-digit date, and the other is to create a date field based on the document (prerequisite: The table has a creation time field)
Create procedure usp_OrderNumberGenerate
@ Prefix varchar (50)
As
Declare @ count int
Declare @ midcode varchar (3)
Declare @ Digits int = 3
Declare @ orderNumber varchar (50)
Select @ count = COUNT (*) from [order] where DATEDIFF (day, createtime, GETDATE () = 0
If (@ count = 0) -- if no document is displayed on the current day, the serial number is 001.
-- Print @ count
Select @ orderNumber = @ prefix + '001'
Else -- maximum number of sequential logs with logs on the current day + 1
Select @ midcode = max (substring (midcode, 10, 3) + 1
From [order]
Where DATEDIFF (day, createtime, GETDATE () = 0
Select @ orderNumber = @ prefix + RIGHT (REPLICATE ('0', @ Digits)
+ CAST (@ midcode as VARCHAR), @ Digits)
Print @ ordernumber