复制代码 代码如下:
create table [order]
(
code varchar(50),
createtime datetime
)
–应用 usp_ordernumbergenerate(@prefix = ‘prc100701’)
–传入前缀 大类+单据编码+6位日期
–获取当日该类单据最大流水号（需按日归零）
–此处判断有两种方法：一种是根据传入6位日期判断；另一种根据单据创建日期字段（前提：表有创建时间字段）
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) –当日无单据情况 流水号为001
–print @count
select @ordernumber = @prefix + ‘001’
else –当日有单据情况 最大流水号+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