SQL 2005 Paging Method Using CTE(Common Table Expressions)

Following procedure used to get Customers Details with Paging.


Create procedure GetCustomersWithPaging

@PageSize INT, --No of Records per Page
@PageNumber INT--selected Page Number

AS
begin
set nocount on

Declare @FirstRow INT
Declare @LastRow INT

SELECT @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

WITH Members AS
(
SELECT CustomerID,ContactName,Address,
ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS RowNumber
FROM Customers
)
SELECT RowNumber,CustomerID,ContactName,Address
FROM Members
WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC;

end
GO