CREATE procedure fuwudanhao(@djno varchar(20) output)
as
declare @a char(8),
@str varchar(20)
set @a=right(CONVERT(varchar(10),getDate(),120),8)
select @str = 'FUWU-'+right(CONVERT(varchar(10),getDate(),120),8)
select @djno = @str+'-' + right(cast(100+( select count(*)+1 from fuwu_dan where fuwu_no like '%'+@a+'%') as varchar(10)),2)
as
declare @a char(8),
@str varchar(20)
set @a=right(CONVERT(varchar(10),getDate(),120),8)
select @str = 'FUWU-'+right(CONVERT(varchar(10),getDate(),120),8)
select @djno = @str+'-' + right(cast(100+( select count(*)+1 from fuwu_dan where fuwu_no like '%'+@a+'%') as varchar(10)),2)
CREATE procedure fuwudanhao(@djno varchar(20) output)
as
declare @a char(8), @str varchar(20),@nn int
declare @sql nvarchar(4000)
set @a=right(CONVERT(varchar(10),getDate(),120),8)
select @str = 'FUWU-'+right(CONVERT(varchar(10),getDate(),120),8)
select @sql='select @n=count(*)+1 from fuwu_dan where fuwu_no like ''%'+@a+ ''''
exec sp_executesql @sql ,
N'@n int output',
@nn output
select @nn=100+@nn
select @djno = @str+'-' + right(@nn,2)
上面的服务单号不能删除,如果删除就有重复的服务单号发生。
declare @a char(8),
@str varchar(20)
set @a=right(CONVERT(varchar(10),getDate(),120),8)
select @str = 'FUWU-'+right(CONVERT(varchar(10),getDate(),120),8)
select @djno = @str+'-' + right(cast(100+( select case when max(right(fuwu_no,2)) is null then 1 else max(right(fuwu_no,2))+1 end as NewMaxID from fuwu_dan where fuwu_no like '%'+@a+'%') as varchar(10)),2)思路
1。按你的条件取出fuwu_no最后两位的最大值。如果是null,取1,否则就加1
2。对取出的值格式化。