用存储过程生成记录编号
CREATE procedure up_GetBeautifulOrderNumber(@orderNumber char(12) out)
as
begin
declare @number char(11)--编号的后11位
declare @maxDate decimal--从数据库表中当前最大的编号中取出的日期
declare @currentDate decimal --当前日期
declare @currentNumber char(12) --数据库表中当前最大的编号
declare @day char(2) --当前日期,如果是一位数,需要将其转换为两位数
set @day=(select case len(datename(d,getdate()))
when 1 then '0'+ cast(datename(d,getdate()) as char(1))
when 2 then cast(datename(d,getdate()) as char(2)) end)
set @currentDate=cast((datename(yyyy,getdate())+datename(mm,getdate())+ @day) as decimal)
if not exists(select top 1 OrderID from Orders order by OrderID desc)
begin
set @number=cast((@currentDate *1000) as char(11))
end
else
begin
set @currentNumber=(select top 1 OrderID from Orders order by OrderID desc)
set @maxDate=cast(substring(@currentNumber, 2,8) as decimal)
if @maxDate<@currentDate
begin
set @number=cast(@currentDate *1000 as char(11))
end
else if @maxDate=@currentDate
begin
set @number=cast((cast(substring(@currentNumber,2,12) as decimal)+1) as char(11))
end
else
begin
print @maxDate
declare @errMessage varchar(100)
set @errMessage=cast(@maxDate as char(10))+ '错误,数据库表中纪录的最大日期有错误,请与数据库管理员联系。'
raiserror(@errMessage, 16, 1)
end
end
set @orderNumber='B'+@number
end
原文地址:https://www.cnblogs.com/jiangshaofen/p/711772.html