create procedure sp_1 @id int output as declare @sql nvarchar(4000) select @id=isnull(max(id),1) from tablename set @sql='select top '+cast(@id as varchar) +' identity(int,1,1) id into ##tem from syscolumns a,syscolumns b,syscolumns c' exec sp_executesql @sql if exists(select * from ##tem where not exists(select * from tablename where tablename.id=##tem.id)) select @id=min(id) from ##tem where not exists(select * from tablename where tablename.id=##tem.id) else set @id=@id+1 drop table ##tem go --调用 declare @id int exec sp_1 @id output print @id
as
declare @sql nvarchar(4000)
select @id=isnull(max(id),1) from tablename
set @sql='select top '+cast(@id as varchar)
+' identity(int,1,1) id into ##tem from syscolumns a,syscolumns b,syscolumns c'
exec sp_executesql @sql
if exists(select * from ##tem
where not exists(select * from tablename where tablename.id=##tem.id))
select @id=min(id) from ##tem
where not exists(select * from tablename where tablename.id=##tem.id)
else
set @id=@id+1
drop table ##tem
go
--调用
declare @id int
exec sp_1 @id output
print @id