Task 14: Pagination

I’m sharing a pagination method that I used in my scripts with a Common Table Expression and the RowNumber function and this seems to be the more elegant method to display a number of lines per page. You can integrate the CTE in a stored procedure or add it as a dynamic script.

DECLARE @PageIndex INT = 1, @Pagesize INT = 10

;With CTEPage

AS

(

SELECT 

ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW,

PageId, 

PageName 

FROM PaginationTable

) 

SELECT PageId, PageName FROM CTEPage

WHERE RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

GO

The PageSize and PageIndex can also be added as parameters in a stored procedure:

CREATE PROCEDURE [dbo].[Report_pagination]
   @PageSize INT = 10, @Page INT =1
AS
SET NOCOUNT ON;
CREATE TABLE #temp ( ...)

;WITH PageNumbers AS
(
SELECT 
  columns,
          ROW_NUMBER() OVER (ORDER BY col_name) AS rownum

FROM #temp
 LEFT JOIN table_name
)

SELECT  *
FROM    PageNumbers
WHERE   rownum  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)
ORDER BY col_name