Get Paging Data from Sql Server

Why Custom Paging?
Custom paging allows you to get limited number records from a large database table that saves processing time of your database server as well as your application server and makes your application scalable, efficient and fast.

In this article, I am going to explain how to create a stored procedure in SQL Server 2005 that allows you to pass startRowIndex and pageSize as a parameter and return you the number of records starting from that row index to the page size specified. It was possible in the SQL Server 2000 too but it was not as easy as in SQL Server 2005 is.

-- EXEC LoadPagedArticles 10, 5CREATE PROCEDURE LoadPagedArticles 

-- Add the parameters for the stored procedure here

@startRowIndex int,

@pageSize int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- increase the startRowIndex by 1 to avoid returning the last record again
SET @startRowIndex = @startRowIndex + 1 

 BEGIN

       SELECT * FROM (

                Select *, 

                ROW_NUMBER() OVER (ORDER BY registration_no ASC) as RowNum

                FROM grievance

              ) as grievance

       WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1

       ORDER BY registration_no ASC

  END

END

GO

Run It:-

exec LoadPagedArticles 6,5