根据搜索到的方法,改进为自定义函数
通过下列语句获取当前最大的订单号
select isnull(left(max(XmId),8)+right('0000'+cast(convert(int,right(max(XmId),4))+1 as varchar),4),CONVERT(varchar(100), getdate(), 112)+'0001') from mt_Work where XmId like ''+CONVERT(varchar, getdate(), 112)+'%'
自定义函数,得到订单号
creaate function dbo.createNetID() -----------得到自定义编号(日期+5位数字)例如:2013070200002
returns nvarchar(50)as
begin
declare @ID nvarchar(50)
declare @num int
select @ID=isnull(left(max(gid ),8)+
right('00000'+cast(convert(int,right(max(gid ),5))+1 as varchar),5),CONVERT(varchar(100), getdate(), 112)+'00001')
from table1 where gid like ''+CONVERT(varchar, getdate(), 112)+'%'
select @num=count(*) from table1 where gid=@ID
while(@num>0)
begin
select @ID=isnull(left(max(gid),8)+
right('00000'+cast(convert(int,right(max(gid),5))+1 as varchar),5),CONVERT(varchar(100), getdate(), 112)+'00001')
from table1 where gid like ''+CONVERT(varchar, getdate(), 112)+'%'
select @num=count(*) from table1 where gid=@ID
end
return @ID
end