How to page using stored procudure.

An example t paging result using stored procudure.

 

USE [LBY]
GO

/****** Object:  StoredProcedure [dbo].[UserPaged]    Script Date: 07/15/2011 10:21:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UserPaged]
( @PageIndex int, @PageSize int )

AS BEGIN
 
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex ( IndexId int IDENTITY (1, 1) NOT NULL, ID int )

-- Insert into the temp table
INSERT INTO #PageIndex
SELECT ID FROM Users ORDER BY ID DESC

-- Return total count
SELECT COUNT(ID) FROM Users

-- Return paged results
SELECT O.* FROM Users O, #PageIndex PageIndex
WHERE O.ID = PageIndex.ID
AND PageIndex.IndexID > @PageLowerBound
AND PageIndex.IndexID < @PageUpperBound
ORDER BY PageIndex.IndexID

END
GO