常用存储过程-用来获取某记录的上一条及下一条记录

功能：用于在指定条件中获取某记录的上一条及下一条记录。
用于：在记录详细信息页面显示某记录详细信息，并可单击上一条及下条快速浏览相关记录。

 

代码

create PROCEDURE [ dbo ] . [ Sys_Common_GetPrivious_NextRecord ]
@currentId int ,
@strSql nvarchar ( 3000 )
AS
DECLARE @theSql nvarchar ( 3000 )
SET @theSql = ' declare RecordCursor cursor FOR ' + @strSql
EXEC sp_executesql @theSql
-- FOR EXAMPLE select id,dan FROM User Where dept=10002

declare @privious_Id int
declare @privious_Dan nvarchar ( 255 )
declare @next_Id int
declare @next_Dan nvarchar ( 255 )
declare @tempId int
DECLARE @tempDan nvarchar ( 255 )

set @privious_Id = 0
set @next_Id = 0
set @tempId = 0
SET @tempDan = ''

open RecordCursor
fetch next from RecordCursor into @tempId , @tempDan

while ( @@fetch_status = 0 )
BEGIN
if ( @tempId = @currentId )
BEGIN
fetch next from RecordCursor into @next_Id , @next_Dan
break
end
set @privious_Id = @tempId
SET @privious_Dan = @tempDan
fetch next from RecordCursor into @tempId , @tempDan
end
close RecordCursor
DEALLOCATE RecordCursor
select @privious_Id AS privious_Id, @privious_Dan AS privious_Dan, @next_Id AS next_Id, @next_Dan AS next_Dan

 

转载于:https://www.cnblogs.com/ruihua/archive/2010/08/08/1795186.html