Query Pagination

PHOTO EMBED

Mon Jul 04 2022 22:47:30 GMT+0000 (UTC)

Saved by @dhfinch #sqlserver

DECLARE @RowsOfPage AS INT
DECLARE @PageNumber AS INT
-- DECLARE @MaxTablePage AS FLOAT 
SET @PageNumber=1
SET @RowsOfPage=10
Select MaintenanceRecord.*, 
PM_StationsList.StationName, PM_StationsList.StationDescription, PM_StationsList.StationType,
LookupLists.ItemDescription, LookupLists.IntValue AS Repeat, LookupLists.strValue as RepeatUnit 
FROM [MaintenanceRecord] 
LEFT OUTER JOIN PM_StationsList ON PM_StationsList.FactoryStationID=MaintenanceRecord.StationID
LEFT OUTER JOIN LookupLists ON ( LookupLists.ListName='MaintenanceItem' AND LookupLists.ItemLookup=MaintenanceRecord.ItemCode)
ORDER BY ItemDueDate DESC
-- =====================================
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
-- SET @PageNumber = @PageNumber + 1
-- =====================================
content_copyCOPY