create table [order]
(
code varchar(50),
createtime datetime
)

--應(yīng)用 usp_ordernumbergenerate(@prefix = 'PRC100701')
--傳入前綴 大類+單據(jù)編碼+6位日期
--獲取當(dāng)日該類單據(jù)最大流水號（需按日歸零）
--此處判斷有兩種方法：一種是根據(jù)傳入6位日期判斷；另一種根據(jù)單據(jù)創(chuàng)建日期字段（前提：表有創(chuàng)建時間字段）
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) --當(dāng)日無單據(jù)情況 流水號為001
--print @count
select @orderNumber = @prefix + '001'
else --當(dāng)日有單據(jù)情況 最大流水號+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