27,579
社区成员
发帖
与我相关
我的任务
分享
--创建例表
create table ArchJzPrj(abc varchar(20))
insert into ArchJzPrj select 'I11-123' union all select 'E50-001'
go
--创建存储过程
create procedure addrow
(@flg varchar(10))
as
begin
set @flg=@flg+'-'
if exists(select 1 from ArchJzPrj where abc like @flg+'%')
insert into ArchJzPrj
select @flg+right('00'+ltrim(convert(int,replace(abc,@flg,''))+1),3) from(
select MAX(abc)abc from ArchJzPrj where abc like @flg+'%'
)t
else
insert into ArchJzPrj select @flg +'001'
end
go
--调用存储过程插入
exec addrow 'E51'
select * from ArchJzPrj
/*
abc
--------------------
I11-123
E50-001
E51-001
(3 行受影响)
*/
exec addrow 'E50'
select * from ArchJzPrj
/*
abc
--------------------
I11-123
E50-001
E51-001
E50-002
(4 行受影响)
*/
go
drop table ArchJzPrj