ROW_NUMBER and OVER in SQL Server Paging
In SQL Server 2000 paging was very diffecult, but with SQL Server 2005 new function ROW_NUMBER() and OVER have made life easy.
Declare @Top int
Declare @Start int
SET @Top = 5
SET @Start = 10
SELECT TOP(@Top) * FROM
(
select row_number() over(ORDER by LastName) as num, OrderNumber from Customers
) as tbl
WHERE tbl.num > @Start
The above code will get you top 5 records from Customers table starting at row number 10, you can use it in stored procedure to retrive rows.
@Top = number of rows.
@Start = Starting row number