select cast(ID AS int)+1 as id from ( select '001' as id union all select '002' union all select '003' union all select '004' union all select '005' ) a id 2 3 4 5 6
if OBJECT_ID('fc_test') is not null drop function fc_test go create function fc_test ( @s varchar(3) ) returns varchar(3) as begin declare @str varchar(3) set @str=ltrim(cast(@s AS int)+1) while LEN(@str)<3 begin set @str='0'+@str end return @str endif OBJECT_ID('ta') is not null drop table ta go create table ta (id varchar(3)) insert into ta select '001' as id union all select '002' union all select '003' union all select '004' union all select '005'select fc_test(id) from ta
Create proc GetID @ID varchar(10) AS declare @LenOfID varchar(10) declare @sql varchar(max) set @LenOfID=cast(LEN(@ID) as varchar(10)) set @sql='select Right(REPLICATE(''0'','+@LenOfID+')' set @sql=@sql+'+CAST(CAST ('+@ID+' as Int)+1 as varchar('+@LenOfID+')),'+@LenOfID+')' exec (@sql)--执行: exec GetID '0023' --结果:-------- 0024(1 行受影响)
from
( select '001' as id
union all
select '002'
union all
select '003'
union all
select '004'
union all
select '005'
) a
id
2
3
4
5
6
select right('00' +ltrim(@i),3) -- 001set @i = @i + 1
select right('00' +ltrim(@i),3) -- 002
select right('00' + ltrim(@s+1), 3) -- 010
drop function fc_test
go
create function fc_test
(
@s varchar(3)
)
returns varchar(3)
as
begin
declare @str varchar(3)
set @str=ltrim(cast(@s AS int)+1)
while LEN(@str)<3
begin
set @str='0'+@str
end
return @str
endif OBJECT_ID('ta') is not null
drop table ta
go
create table ta (id varchar(3))
insert into ta
select '001' as id
union all
select '002'
union all
select '003'
union all
select '004'
union all
select '005'select fc_test(id)
from ta
Create proc GetID
@ID varchar(10)
AS
declare @LenOfID varchar(10)
declare @sql varchar(max)
set @LenOfID=cast(LEN(@ID) as varchar(10))
set @sql='select Right(REPLICATE(''0'','+@LenOfID+')'
set @sql=@sql+'+CAST(CAST ('+@ID+' as Int)+1 as varchar('+@LenOfID+')),'+@LenOfID+')'
exec (@sql)--执行:
exec GetID '0023'
--结果:--------
0024(1 行受影响)