这个用一个函数去实现好了例如现在数据库中的号码ID为20120220-002,传进来的值为 20120221,那么查询值declare @IID varchar(20) set @IID = '20120221' declare @ID varchar(20) set @ID = '' select top 1 @ID = ID from TableA where ID like '20120221%' order by ID desc; if @ID = '' begin set @IID = @IID + '-001'; end else begin declare @I int
set @ID = substring(@ID,len(@ID) - 3); @I = Convert(int,@ID) + 1; set @ID = convert(varchar(3),@I) if len(@ID) = 1 begin set @ID = '00' + @ID end else if len(@ID) = 2 begin set @ID = '0' + @ID end set @IID = @IID + '-' + @ID end
20120221,那么查询值declare @IID varchar(20)
set @IID = '20120221'
declare @ID varchar(20)
set @ID = ''
select top 1 @ID = ID from TableA where ID like '20120221%' order by ID desc;
if @ID = ''
begin
set @IID = @IID + '-001';
end
else
begin
declare @I int
set @ID = substring(@ID,len(@ID) - 3);
@I = Convert(int,@ID) + 1;
set @ID = convert(varchar(3),@I)
if len(@ID) = 1
begin
set @ID = '00' + @ID
end
else if len(@ID) = 2
begin
set @ID = '0' + @ID
end
set @IID = @IID + '-' + @ID
end
return @IID