/*
过程名 getnum
功能:  取得指定表的下一个可用的编号
输入参数:   @tablname 表名
@fileldname 即要查询的字段名.在不同的表编号所写的列名不是一样的.如有id num等
@num 返回下一个可用的编号  
*/
create procedure dbo.getnum  
@tablname varchar(8),
@fileldname varchar(8),
@num  varchar(6) output
as
declare @sql  varchar(200)
declare @oldnum int,@newnum varchar(6)
set @sql=''  
set @sql='declare mycursor cursor for   select '+ rtrim(@fileldname) + 
' from ' + rtrim(@tablname) 
exec(@sql)
if @@error=0
begin
set  @oldnum=1
open mycursor
fetch next from mycursor into @newnum
while @@fetch_status=0
begin
if replicate(0,3-len(@oldnum))+cast(@oldnum as varchar) = @newnum 
set @oldnum=@oldnum+1
else
begin 
set @num=replicate(0,3-len(@oldnum))+cast(@oldnum as varchar) 
return  
end
fetch next from mycursor into @newnum 
end
close mycursor
deallocate mycursor
set @num=replicate(0,3-len(@oldnum))+cast(@oldnum as varchar) 
--如果顺序没有被打断
return
end   
else
raiserror
('该表或列名不存在.请检查输入的表名和列名是否正确?',7,1)
go