Custom Pagging in SQL Server using ROW_NUMBER Function

This is simple post to implement the pagination in SQL Server using Analytical functions.

-- Variable Declaration
DECLARE @PageLimit smallint, @CurrentPageNumber smallint
SET @PageLimit = 10 -- Setting Page Limit to 10
SET @CurrentPageNumber = 10 -- Current Page Number
DECLARE @StartRow INT
DECLARE @EndRow INT
-- Calculating the starting row
SET @StartRow = ((@CurrentPageNumber-1) * @PageLimit)
-- Calculation the Ending row
SET @EndRow = @StartRow + @PageLimit
-- Query to get the actual records based on Order by Clase
SELECT * FROM
(
SELECT ProductID,
Name,
ProductNumber,
ROW_NUMBER()OVER(ORDER BY Name) AS RowNumber
FROM Production.Product
) v
WHERE v.RowNumber > @StartRow AND v.RowNumber <= @EndRow