Paging in SQL Server 2005 using Common Table Expression

Here is the Code for Paging Operation in SQL Server 2005 using CTE.

declare @start int,@end int

select @start = 1, @end = 20;

WITH SiteInfo AS
(
SELECT SiteName, Latitude,Longitude,
ROW_NUMBER() OVER (ORDER BY CityID) AS SNo
FROM Sites
)
SELECT SNo,SiteName, Latitude,Longitude
FROM SiteInfo
WHERE SNo BETWEEN @start AND @end

No comments: