CREATE PROCEDURE [dbo].[PROC_GetPriviousAndNextDetailContent]
@Index varchar(20),--表主键
@Table varchar(100),--从哪个表获取数据
@Columns varchar(100),--需要获取哪些字段
@OrderStr varchar(100),--排序字段及方式
@Where1 varchar(100),--row_number中的初步过滤条件
@Where2 varchar(100)--当前要查询详细内容的ID条件
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @sql nvarchar(1000),@rowid int,@Previousid int,@Nextid int
set @sql=N‘select @rowid=rowindex from(select ‘+@Columns+‘,ROW_NUMBER() over(order by ‘+@OrderStr+‘)rowindex from ‘+@Table+‘ where ‘+@Where1+‘ )rt where ‘+@Where2
--print @sql
exec sp_executesql @sql,N‘@rowid int output‘,@rowid=@rowid output
--获取前一条ID
set @sql=N‘select @Previousid=‘+@Index+‘ from(select ‘+@Columns+‘,ROW_NUMBER() over(order by ‘+@OrderStr+‘)rowindex from ‘+@Table+‘ where ‘+@Where1+‘ )rt where rowindex=‘+cast((@rowid-1) as varchar)
exec sp_executesql @sql,N‘@Previousid int output‘,@Previousid=@Previousid output
if @Previousid is null
set @Previousid=0
--获取后一条ID
set @sql=N‘select @Nextid=‘+@Index+‘ from(select ‘+@Columns+‘,ROW_NUMBER() over(order by ‘+@OrderStr+‘)rowindex from ‘+@Table+‘ where ‘+@Where1+‘ )rt where rowindex=‘+cast((@rowid+1) as varchar)
exec sp_executesql @sql,N‘@Nextid int output‘,@Nextid=@Nextid output
if @Nextid is null
set @Nextid=0
--print @Previousid
--print @Nextid
--获取最后完整数据
exec(‘select ‘+@Columns+ ‘,‘+@Previousid+‘ Previousid,‘+@Nextid+‘ Nextid from ‘+@Table+‘ where ‘+@Where2)
END
表结构：
Id two three
1 2 3
4 5 6
7 8 9
根据传入参数“4”查询结果：
1 7 5 6