I am having pagination issue on executing below sql query. records from 1 to 10 are shown but on pagination same sequence is coming
What I have tried:
My query is as below:
DECLARE @ip_Process varchar(20)
DECLARE @p_PageNo INT
,@p_PageSize INT
,@TimeInterval INT
DECLARE @TopRecords INT
DECLARE @LeftStart INT
--Top records to be read
SET @TopRecords= (@p_PageNo + 1) * @p_PageSize
--Starting records from left
SET @LeftStart= (@p_PageNo) * @p_PageSize
SELECT
TOP ( @TopRecords) ROW_NUMBER() OVER (ORDER BY Slnum ) AS SlnO,
[ID]
,ProcessName
,TableName --Updated as per Onsite Need on 21.6.2021
,ProcessCode
,NodeName,
Timestamp
FROM(
SELECT
ROW_NUMBER() OVER (ORDER BY [ID] ) AS Slnum,
[ID]
,ProcessName
,TableName --Updated as per Onsite Need on 21.6.2021
,ProcessCode
,NodeName,
Timestamp
FROM(
SELECT
[UBSPostingID] as ID
,'UBS Inward Process' as ProcessName
--Updated as per Onsite Need on 21.6.2021 Start
,'DDS_UBSPostingsQueue' as TableName
--Updated as per Onsite Need on 21.6.2021 End
,'11' as ProcessCode
,NodeName,
Timestamp
FROM
DDS_UBSPostingsQueue
WHERE ISNULL(NodeName,'')!='' AND ISNULL(Timestamp ,'')!=''
AND (DATEADD(MINUTE,@TimeInterval,Timestamp)) < GETDATE() --Timestamp<(DATEADD(MINUTE,@TimeInterval,GETDATE()))
Union All
SELECT
[SFTPFileId] as ID
,'SFTP Upload' as ProcessName
--Updated as per Onsite Need on 21.6.2021 Start
,'DDS_SFTPFileTransferQueue' as TableName
--Updated as per Onsite Need on 21.6.2021 End
,'12' as ProcessCode
,NodeName,
Timestamp
FROM
DDS_SFTPFileTransferQueue
WHERE ISNULL(NodeName,'')!='' AND ISNULL(Timestamp ,'')!=''
AND (DATEADD(MINUTE,@TimeInterval,Timestamp)) < GETDATE() --Timestamp<(DATEADD(MINUTE,@TimeInterval,GETDATE()))
) p
WHERE (P.ProcessCode=@ip_Process OR @ip_Process='') ) PR WHERE Slnum> @LeftStart