Paging via a SQL Server Stored Procedure

The third and final approach involves a stored procedure. This is the most efficient approach because, unlike ADO and getrows which both return the entire set of records to the Web server, the stored procedure returns only the records that are needed for the current page.

Once again, the paging algorithm is roughly the same as ADO, but here the current page and the page size are passed into the stored procedure as input parameters. The stored procedure then selects the set of records needed for the current page by setting up a temporary table with an identity field and using the identity field to determine which records should be returned, given the current page and page size.

CREATE PROCEDURE "sprocInformationTechnologyProjects"  @Page int, @Size int  AS  DECLARE @Start int, @End int  BEGIN TRANSACTION GetDataSet  SET @Start = (((@Page - 1) * @Size) + 1) IF @@ERROR <> 0  GOTO ErrorHandler  SET @End = (@Start + @Size - 1) IF @@ERROR <> 0  GOTO ErrorHandler  CREATE TABLE #TemporaryTable (  Row int IDENTITY(1,1) PRIMARY KEY,  Project varchar(100),  Buyer int,  Bidder int,  AverageBid money ) IF @@ERROR <> 0  GOTO ErrorHandler  INSERT INTO #TemporaryTable SELECT ... // Any kind of select statement is possible with however many joins //  as long as the data selected can fit into the temporary table. IF @@ERROR <> 0  GOTO ErrorHandler  SELECT Project, Buyer, Bidder, AverageBid FROM #TemporaryTable WHERE (Row >= @Start) AND (Row <= @End) IF @@ERROR <> 0  GOTO ErrorHandler  DROP TABLE #TemporaryTable  COMMIT TRANSACTION GetDataSet RETURN 0  ErrorHandler: ROLLBACK TRANSACTION GetDataSet RETURN @@ERROR

Reference : http://www.15seconds.com/issue/010308.htm

 http://blog.sqlauthority.com/2007/06/11/sql-server-2005-t-sql-paging-query-technique-comparison-over-and-row_number-cte-vs-derived-table/