Custom Paging Using Sql Server 2005

Till Sql server 2000, we will create a temp table with identity column to implement custom paging. With the introduction of Sql Server 2005, the implementation of custom paging is made simpler with a 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)

In  the above stored procedure, i have obtained the startrow and endrow using @RowIndex and @MaxRows parameter. The subsequent select query will select only those records whose Row_Number() falls between the startrow and endrow. Please note that ROW_NUMBER() will return the row index for every row in the resultset and thus provides a unique value column that can identify each row.

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