Common stored procedures-used to obtain the previous and next records of a record

Function: used to obtain the first and next records of a record in a specified condition.
Used to: display the detailed information of a record on the record details page, and click the previous and next record to view the relevant records quickly.

 

Code

 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