CREATE PROCEDURE [dbo]. [Proc_getpriviousandnextdetailcontent]
@Index varchar,--Table primary Key
@Table varchar,--from which table gets the data
What fields @Columns varchar,--need to get
@OrderStr varchar,--sort fields and how
Preliminary filtration conditions in the @Where1 varchar,--row_number
@Where2 varchar (100)--The ID condition for which the details are currently being queried
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, @rowid int, @Previousid int, @Nextid int
Set @sql =n ' select @rowid =rowindex from (SELECT ' [E-mail protected]+ ', row_number () over (order by ' [email protected]+ ') rowindex from ' [email protected]+ ' WHERE ' [email protected]+ ') RT where ' [email protected]
--print @sql
EXEC sp_executesql @sql, N ' @rowid int output ', @[email protected] Output
--Get the previous ID
Set @sql =n ' Select @Previousid = ' [email protected]+ ' From (select ' [E-mail protected]+ ', row_number () over (order by ' [email protected]+ ') rowindex from ' [email protected]+ ' WHERE ' [email protected]+ ') RT where rowindex= ' +cast ((@rowid-1) as Varcha R
EXEC sp_executesql @sql, N ' @Previousid int output ', @[email protected] Output
If @Previousid is null
Set @Previousid =0
--Get the next ID
Set @sql =n ' Select @Nextid = ' [email protected]+ ' From (select ' [E-mail protected]+ ', row_number () over (order by ' [email protected]+ ') rowindex from ' [email protected]+ ' WHERE ' [email protected]+ ') RT where rowindex= ' +cast ((@rowid + 1) as Varcha R
EXEC sp_executesql @sql, N ' @Nextid int output ', @[email protected] Output
If @Nextid is null
Set @Nextid =0
--print @Previousid
--print @Nextid
--Get the final complete data
EXEC (' select ' [email protected]+ ', ' [email protected]+ ' previousid, ' [email protected]+ ' nextid from ' [Email protected]+ ' where ' [email protected]
END
table structure:
Id Three
1 2 3
4 5 6
7 8 9
Query results based on incoming parameter "4":
1 7 5 6