Pagination Example

SQL Server’s TOP clause allows you to SELECT n rows from a result set. 

Unfortunately, these rows are always from the top.  If for example you have a large set of rows, and you have a front-end interface that allows you to show a given page, SQL Server does not have a native method for scrolling down p pages and retrieving only the rows for that page.

But, thanks to the  ROW_NUMBER()  built-in function in combination with a Common Table Expression (CTE), you can create a proc that does exactly what you need.

USE Admin

IF Object_id('dbo.PaginationExample') Is Not Null

      DROP PROC dbo.PaginationExample

GO

CREATE PROC dbo.PaginationExample

      @PageNum int,

      @RowsPerPage int

AS

/*    DATE        AUTHOR            REMARKS

      5/1/12            PPaiva            Initial creation.

     

      DESCRIPTION

            Selects a set of rows for a given page.

            To use, simply swap your table/view name for

                  vwServerDBFile, and change the 

                  ORDER BY clause in the CTE.

           

      USAGE

            Exec PaginationExample 2, 5

            Exec PaginationExample 3, 10

           

      DEBUG

            SELECT *

            FROM vwServerDBFIle

            ORDER BY Server, DB, Usage desc, Filename

*/

SET NOCOUNT ON

DECLARE @StartRow int,

            @EndRow int,

            @Msg varchar(300)

SET @StartRow = (@RowsPerPage * (@PageNum - 1) + 1)

SET @EndRow = @StartRow + (@RowsPerPage - 1)

; WITH cteOutput

AS ( 

      SELECT ROW_NUMBER() OVER (ORDER BY Server, DB, Usage desc, Filename) AS RowNum,

                  *

      FROM vwServerDBFIle

)

SELECT *

FROM cteOutput

WHERE RowNum BETWEEN @StartRow AND @EndRow

ORDER BY RowNum