Implement paging with CTE and Row_Number in Sql Server.

We needed to implement paging and this is how we at SyneITY went about it. Since we were using Sql Server 2008 we decided to go for using the Row_Number() function along with CTE (Common Table Expression).

Procedure

Create proc [dbo].[getMovies]
(
@PageNo Int,
@NoOfRowsInPage Int
)
As
Begin
Declare 
@RowNoFrom Int = @NoOfRowsInPage * (@PageNo - 1),
@RowNoTo Int = (@NoOfRowsInPage * @PageNo);

With CTE As
(
Select
ROW_NUMBER() Over (Order By ReleaseDate desc) RowNo,
T.Id,
T.Name
From
moviesTable T with(Nolock)            
)

Select 
RowNo,
Id,
Name        
From CTE where RowNo > @RowNoFrom And RowNo < @RowNoTo
end

Test the Procedure

Exec getMovies @PageNo = 1, @NoOfRowsInPage = 10
Exec getMovies @PageNo = 2, @NoOfRowsInPage = 10

Downloads

you can download the entire script here to try it out.

Written by Pramod Pallath