Custom Paging Using Sql Server 2005

Till Sql server 200, we will create a temp table with identity column to implement custom paging. With the introduction of Sql Server 2005, the implementation of Sql 2005 is made simple with the new feature called Row_Number().

The below stored procedure will accept the start row index and number of records (PageSize) to return the records that belongs to only that particular page.

EXEC GetUsers 1,10

Stored Procedure
ALTER PROC [dbo].[GetUsers]
(
@RowIndex INT ,
@MaxRows INT)
AS
DECLARE @StartRow INT
DECLARE @EndRow INT

SET @StartRow = @RowIndex
SET @EndRow = @StartRow + @MaxRows

SELECT * FROM (
SELECT     UserID,FirstName,LastName, ROW_NUMBER() OVER (ORDER BY UserID) AS ROW
FROM         CD_Users) As NumberedUsers
WHERE ROW BETWEEN @StartRow AND (@EndRow-1)

Read my article, Custom GridView Paging with ObjectDataSource Control with ASP.Net 2.0