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

Paging in SQL Server 2005 Database

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

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

Begin

Insert Into Dev_Products (Prod_ID,Prod_Name)

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

      Set @IntCount= @IntCount + 1

End

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

)

 

AS

BEGIN

 

      DECLARE @FirstRec int, @LastRec int 

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

      SELECT  @LastRec  = (@PageNumber * @PageSize)

 

      SELECT 

            Prod_ID,

            Prod_Name

      FROM

      (

            SELECT 

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

                  Prod_ID,

                  Prod_Name

            FROM Dev_Products

      ) AS ProdWithRowNumbers

      WHERE  Row > @FirstRec AND Row <= @LastRec

 

END

 

 

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 DevASP.net