Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
SQL Server

Paging in SQL Server 2005 Database

Author: Babar
Download Source Code : 703_Paging Records Using SQL Server

In normal situation we develop webpage and use paging property of Datagrid and Gridview. But if we use Data List that doesn’t support paging property or we need to control paging through SQL Server

Below article will help you to achieve this functionality easily.

Paging can be done using ROW_NUMBER function in SQL Server 2005 that returns a sequential number series starts always with 1.

Let’s start with creating a table and inserting some records.

Create Table Dev_Products


      Prod_ID           Int,

      Prod_Name         Varchar(255)


I prepared one query which will enter some records for us.


Declare @IntCount Int

Set @IntCount=1


While @IntCount <= 30


Insert Into Dev_Products (Prod_ID,Prod_Name)

Values (@IntCount,'DevASP'+ Convert(varchar,@IntCount))

      Set @IntCount= @IntCount + 1


Select * from Dev_Products


This will insert 30 records as we specified in While condition.


We have populated table and we need to create a procedure with paging functionality.




Create Procedure Proc_Dev_Products_GetAll


 @PageNumber      INT, 

 @PageSize        INT = 10






      DECLARE @FirstRec int, @LastRec int 

      SELECT  @FirstRec = (@PageNumber  - 1) * @PageSize 

      SELECT  @LastRec  = (@PageNumber * @PageSize)








                  ROW_NUMBER() OVER (ORDER BY Prod_ID ASC) AS Row,



            FROM Dev_Products

      ) AS ProdWithRowNumbers

      WHERE  Row > @FirstRec AND Row <= @LastRec





Let me explain to understand you better.

In this stored procedure we will be providing two parameters, PageNumber and PageSize, If you wanted to display 5 records per page then you can pass 5 as a pagesize parameter.


Article Comments
I have surfed so many websites , i really wanted to say this was wonderful and simplest way of paging in sql server. keep it up.

Posted on 11/2/2007 12:37:05 PM by Maqsood

This is good.

Posted on 5/18/2010 7:18:31 AM by Praveen

Add Article Comment:
Name :
Email Address :
Comments :
<< SQL Server 2005 and WildCards characters

Disclaimer - Privacy
© 2002-2017