Paging Stored Procedure in SQL Server 2008/2005

Paging is something every website developer need. To make paging happens, you need to work a lot if you create your logic in any front end but it became very easy if you will use windows partition functions of SQL Server. My previous article was completely dedicated to this topic and in this article, I am going to show you how you can create paging with the help of SQL Server’s Row_Number() function, if you don’t have identity field.
Generally people used to get full record sets in dataset (.NET) and used to show only those records which needs to display, I don’t encourage this practice, fetch only those records from database which needs to display, by this way, you can reduce network traffic and can get good performance.
Anyway, let me show you the magical, handy stored procedure.
Create Table UsersTable
(
      UserID Varchar(25),
      FirstName Varchar(25),
      LastName Varchar(25),
      JoiningDate datetime
)
GO


Insert into UsersTable
select ‘Ritesh’,‘Ritesh’,‘Shah’,GETDATE()-50 union all
select ‘Rajan’,‘Rajan’,‘Shah’,GETDATE()-4 union all
select ‘Bihag’,‘Bihag’,‘Thaker’,GETDATE()-10 union all
select ‘Alka’,‘Alka’,‘Shah’,GETDATE()-30 union all
select ‘Rushik’,‘Rushik’,‘Shah’,GETDATE()-25 union all
select ‘Krunal’,‘Krunal’,‘Shah’,GETDATE()-20 union all
select ‘Bhaumik’,‘Bhaumik’,‘Shah’,GETDATE()-10
GO


select * from UsersTable
go


–creating stored procedure for searching user with paging.
CREATE PROC SearchUser
@PageNum INT, –pass page number you want to see
@UserPerPage INT, –pass number of users, you want to see on one page.
@Date Datetime –you can pass as many varialbe as you want to put in WHERE clause.
AS
BEGIN
      Select * FROM
      (
            SELECT
                  UserID,FirstName,LastName,JoiningDate,ROW_NUMBER() Over(Order by UserID) as rowNum
            FROM
                  UsersTable
            WHERE
                  JoiningDate<=@Date
      ) as t WHERE rowNum BETWEEN (@PageNum1) * @UserPerPage+1 AND @PageNum * @UserPerPage
END
GO


–check whether paging actually works or not
EXEC SearchUser 1,2,‘20090920’
GO


EXEC SearchUser 2,2,‘20090920’
GO


EXEC SearchUser 3,2,‘20090920’
GO
This is just a small demonstration about what you can do, you can make this SP more friendly and customize as per your need.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Advertisements

Paging Stored Procedure in SQL Server 2008/2005

Paging is something every website developer need. To make paging happens, you need to work a lot if you create your logic in any front end but it became very easy if you will use windows partition functions of SQL Server. My previous article was completely dedicated to this topic and in this article, I am going to show you how you can create paging with the help of SQL Server’s Row_Number() function, if you don’t have identity field.
Generally people used to get full record sets in dataset (.NET) and used to show only those records which needs to display, I don’t encourage this practice, fetch only those records from database which needs to display, by this way, you can reduce network traffic and can get good performance.
Anyway, let me show you the magical, handy stored procedure.
Create Table UsersTable
(
      UserID Varchar(25),
      FirstName Varchar(25),
      LastName Varchar(25),
      JoiningDate datetime
)
GO


Insert into UsersTable
select ‘Ritesh’,‘Ritesh’,‘Shah’,GETDATE()-50 union all
select ‘Rajan’,‘Rajan’,‘Shah’,GETDATE()-4 union all
select ‘Bihag’,‘Bihag’,‘Thaker’,GETDATE()-10 union all
select ‘Alka’,‘Alka’,‘Shah’,GETDATE()-30 union all
select ‘Rushik’,‘Rushik’,‘Shah’,GETDATE()-25 union all
select ‘Krunal’,‘Krunal’,‘Shah’,GETDATE()-20 union all
select ‘Bhaumik’,‘Bhaumik’,‘Shah’,GETDATE()-10
GO


select * from UsersTable
go


–creating stored procedure for searching user with paging.
CREATE PROC SearchUser
@PageNum INT, –pass page number you want to see
@UserPerPage INT, –pass number of users, you want to see on one page.
@Date Datetime –you can pass as many varialbe as you want to put in WHERE clause.
AS
BEGIN
      Select * FROM
      (
            SELECT
                  UserID,FirstName,LastName,JoiningDate,ROW_NUMBER() Over(Order by UserID) as rowNum
            FROM
                  UsersTable
            WHERE
                  JoiningDate<=@Date
      ) as t WHERE rowNum BETWEEN (@PageNum1) * @UserPerPage+1 AND @PageNum * @UserPerPage
END
GO


–check whether paging actually works or not
EXEC SearchUser 1,2,‘20090920’
GO


EXEC SearchUser 2,2,‘20090920’
GO


EXEC SearchUser 3,2,‘20090920’
GO
This is just a small demonstration about what you can do, you can make this SP more friendly and customize as per your need.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com