34,837
社区成员




create table T1(Name nvarchar(5) ,Type nvarchar(2), Code nvarchar(5) primary key)
go
--用函数计算最大编号:
create function T1_number(@Type nvarchar(2))
returns nvarchar(5)
as
begin
declare @s nvarchar(5)
set @s=@Type+(select right(1000+isnull(max(Code),0)+1,3) from T1 where Type=@Type)
return @s
end
go
insert T1(Name,Type,Code) values('小红','01',dbo.T1_number('01'))
insert T1(Name,Type,Code) values('小名','01',dbo.T1_number('01'))
insert T1(Name,Type,Code) values('小张','02',dbo.T1_number('02'))
select * from T1
/*
Name Type Code
----- ---- -----
小红 01 01001
小名 01 01002
小张 02 02001
*/
subName subYear subType Code
小红 2007 01 0701001
小名 2005 01 0501001
小张 2007 02 0702001
小刚 2006 03 0601001
小明 2007 01 0701002
小飞 2007 01 0701003
create proc procname
@subname varchar(10),
@subyear varchar(4),
@subtype varchar(2),
@code varchar(7)
as
begin
declare @s varchar(7)
select @s = max(code) from table1 where subtype = @subtype and subyear = @subyear
insert table1
select @subname,@subyear,@subtype,@subyear+@type+ right('000'+ltrim(cast(right(@s,3) as int) + 1),3)
end