求一个自动生成序号的存储过程

[NOLJH]

求教一个存储过程：在SQL2000的表tPsn中有个序号字段PsnNo,要求使用一个存储过程添加记录时能自动生成这个序号，并且优先从这个字段中有间断的序号中选择最小的序号，当这个字段没有间断时，可在最大序号后以步进值1新增序号。
谢过各位高人指点！

[SQL77]

等经典代码,自动生成流水号的函数

[--小F--]

--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
	RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO

--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

--插入资料
BEGIN TRAN
	INSERT tb(col) VALUES(1)
	INSERT tb(col) VALUES(2)
	INSERT tb(col) VALUES(3)
	DELETE tb WHERE col=3
	INSERT tb(col) VALUES(4)
	INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN

--显示结果
SELECT * FROM tb
/*--结果
BH         col 
---------------- ----------- 
BH000001  1
BH000002  2
BH000003  4
BH000004  14
--*/

[dawugui]

[Quote=引用楼主 noljh 的回复:]
    求教一个存储过程：在SQL2000的表tPsn中有个序号字段PsnNo,要求使用一个存储过程添加记录时能自动生成这个序号，并且优先从这个字段中有间断的序号中选择最小的序号，当这个字段没有间断时，可在最大序号后以步进值1新增序号。
    谢过各位高人指点！
[/Quote]

create proc my_proc @rtn OUTPUT
as
begin
if not exists(select 1 from tpsn)
   set @rtn = 1
else
   if exists
   (select min(m.PsnNo) + 1 PsnNo from
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) m,
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) n
   where m.px = n.px - 1 and m.PsnNo <> n.PsnNo + 1)
   select @rtn = min(m.PsnNo) + 1 PsnNo from
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) m,
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) n
   where m.px = n.px - 1 and m.PsnNo <> n.PsnNo + 1
   else
   select @rtn max(PsnNo) + 1 from tpsn
return @rtn 
end
   

[dawugui]

create proc my_proc @rtn OUTPUT
as
begin
if not exists(select 1 from tpsn)
   set @rtn = 1
else
   if exists
   (select min(m.PsnNo) + 1 PsnNo from
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) m,
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) n
   where m.px = n.px - 1 and m.PsnNo <> n.PsnNo + 1)
   select @rtn = min(m.PsnNo) + 1 PsnNo from
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) m,
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) n
   where m.px = n.px - 1 and m.PsnNo <> n.PsnNo + 1
   else
   select @rtn = max(PsnNo) + 1 from tpsn
return @rtn 
end

[dawugui]

create proc my_proc @rtn OUTPUT
as
begin
if not exists(select 1 from tpsn)
   set @rtn = 1
else
   if exists
   (select min(m.PsnNo) + 1 PsnNo from
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) m,
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) n
   where m.px = n.px - 1 and m.PsnNo <> n.PsnNo - 1)
   select @rtn = min(m.PsnNo) + 1 PsnNo from
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) m,
   (select * , px = (select count(1) from tpsn where PsnNo < t.PsnNo) from tpsn t) n
   where m.px = n.px - 1 and m.PsnNo <> n.PsnNo - 1
   else
   select @rtn = max(PsnNo) + 1 from tpsn
return @rtn 
end

[幸运的意外]

关注。

[newdigitime]

insert into xxx c (name,uid)
values('小张',select top 1 a.uid from xxx a where (select * from xxx b where uid=a.uid+1)=0)

[newdigitime]

declare @gg int
select top 1 @gg=a.udi+1 from tb a where not exists(select * from tb b where b.udi=a.udi+1)order by a.udi
insert into tb (name,udi) values('小张',@gg)