--测试数据 select * into id_name from ( select 3 ID,'AA' NAME union select 6 id,'BB' union select 9 ,'CC' union select 2,'DD' union select 7,'EE' union select 2,'FF' ) a--函数建立 create function f_idbetween(@id int) returns @result table( id_num int identity(1,1), [id] int, [name] varchar(20) ) as begin declare @id_num intinsert into @result(id,name) select id,name from id_name order by nameselect @id_num = id_num from @result where id = @iddelete @result where not (id_num = @id_num + 1 or id_num = @id_num - 1)return end--函数调用 select * from f_idbetween(9)--删除测试数据 drop function f_idbetween drop table id_name
select *
into id_name
from
(
select 3 ID,'AA' NAME
union
select 6 id,'BB'
union
select 9 ,'CC'
union
select 2,'DD'
union
select 7,'EE'
union
select 2,'FF'
) a--函数建立
create function f_idbetween(@id int)
returns @result table(
id_num int identity(1,1),
[id] int,
[name] varchar(20)
)
as
begin
declare @id_num intinsert into @result(id,name)
select id,name
from id_name
order by nameselect @id_num = id_num
from @result
where id = @iddelete @result
where not (id_num = @id_num + 1 or
id_num = @id_num - 1)return
end--函数调用
select * from f_idbetween(9)--删除测试数据
drop function f_idbetween
drop table id_name