Using SQL Server Stored Procedure for implementing Custom Paging

Hi

All of us would have implemented Paging in our applications.

Paging is particularly useful if you have lots of records to be displayed on a page and you can't get them displayed in one stretch. Say we have 1000 records to be displayed in a page. In this scenario, we cannot show up all the records in a single stretch in the page. Hence we need to implement Paging functionality whereby users can see a set of records and then click on a Button/Link to view the next set of records.

Paging can be implemented in 3 ways viz., Storing all the records at the browser level through XML, session etc., using built-in paging functionalities provided by controls like .NET Datagrid, using SQL Stored Procedures for retrieving set based results.

Out of the above 3, the SQL Method would be the best approach if you are aiming at Custom Paging Mechanism.

But, its a little tricky. Say you have a table which doesn't have an identity column or any sequential data column which you can use to determine the position of each row, then you need to write your procedure in a way that it can take the start values and end values for the set of records to be displayed.

The following procedure is useful to retrieve records in sets for displaying and useful in implementing custom paging.

Let us consider an Employees Table which doesn't have an Identity Column and we want to fetch sets of records based on the set requested for. Consider the following procedure



CREATE PROCEDURE uspPaging
@nStartValue INT,
@nEndValue INT
AS
SET NOCOUNT ON

DECLARE @tblTempData TABLE
(
nID INT IDENTITY,
EmployeeID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50),
SupervisorID NCHAR(5)
)

INSERT INTO @tblTempData
(
EmployeeID,
LastName,
FirstName,
SupervisorID
)
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo
FROM Employees
ORDER BY
EmployeeID,
FirstName

SELECT EmployeeID,
LastName,
FirstName,
SupervisorID
FROM @tblTempData
WHERE nID BETWEEN @nStartValue AND @nEndValue
ORDER BY
nID ASC


The above procedure will accept 2 parameters @nStartValue and @nEndValue and display the records based on the values. The @tblTempData which we create within this procedure is useful in enforcing the IDENTITY Column which does not exist in the original table.

This procedure can be used when implementing custom paging and can be modified according to the actual requirements.

I welcome your comments.

Cheers and Happy Programming !!!

Print | posted on Monday, April 25, 2005 8:00 AM

Comments on this post

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Very good article
I have a doubt paging . In .NET , if we fetch 1000 records from data base into dataset.Then i think there is no need to go to database again and again, because DataSet is disconnected database, we have all 100 records in dataset, so why i need to go to database again and again.
I think if we put DataSet in ViewState or Cache it can be used in the next post backs to go to the desired page of records. Plz tell me is it a correct process or not.
Thank u
Left by P.Suresh on Jul 04, 2005 7:19 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
But if you are pulling 50,000 items of info from the database and need to display maybe 50 per page... using viewstate would be disasterous, since you'd be posting back 50k records to and from the server. Caching would be better than viewstate, but still hit the database for 50,000 retrievals, taking ages.

With the above method it seems if you really want to just display 50 records on screen per page, the db will only be hit for 50 records.

That said, I'm not looking forward to putting it into place on my database. I have to though, cos there's potential for a user to pull up to 100,000 rows of information *sob*.

My mind is thinking surely there's a way in SQL stored procedures to do a SELECT from say records 150 to 199... must get researching... please reply if you know of a way to do this...
Left by M. Clark on Sep 02, 2005 1:19 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
M. Clark, there is one way; although it involves nested queries.

SELECT TOP [pageSize] *
FROM [tableName]
WHERE [ID] NOT IN (
SELECT TOP [pageSize * currentPage] FROM [tableName] ORDER BY [ID])
ORDER BY [ID]

Just replace the values in square brackets with input parameters or similar.
Left by J.Gregory on Sep 23, 2005 10:58 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Oooh, cheers for that.

It looks much easier than the above dynamic-sproc :).
Left by M. Clark on Oct 04, 2005 9:56 AM

# queries

Requesting Gravatar...
hello sir i need a help
i.e a query
"select * from table where pcity="&cityid&"
it display correctly in an hyper of this page for asc order with the relevant id with asc order how can i get that query plz
Left by bhuvanesh on Oct 27, 2005 7:11 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Hi the posted query is really a good one. The only problem what the user may face is to get the total number of pages. This you can do in different ways do a count(*) from the query and attach the same with your select query. Else you return one more table which returns the count or assign value to a output variable with the count value.

SELECT TOP [pageSize] *
FROM [tableName]
WHERE [ID] NOT IN (
SELECT TOP [pageSize * currentPage] FROM [tableName] ORDER BY [ID])
ORDER BY [ID]

Which seems to be a bad practise if you are sorting based on a text or varchar field. In the first case it's definetely going to be an ID field whcih results in better performance.

Thanks,
Kaladharan.M


Left by Kaladharan on Nov 20, 2006 11:07 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Hi, the query works good but has problems when the data to base the paging on has repeated values. For instance, if your table has an "age" field with only two values and the number of records with one of the values is greater than the records per page, when you apply the query you will loose the records of that age that didn't fit on the first selected page. Any ideas to fix this problem?

Thanks a lot!!
Jaime Gonzalez
Left by jaime_gonz on Nov 28, 2006 8:41 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Jamie G:
The result set would split into multiple pages, which is what you want in 'custom paging'. It would take more than one query to retrieve the whole thing, but it minimizes the amount of data that the client receives. Try it without custom paging and see what the difference in run time is when you have say.. 20,000 rows to pick from... custom paging, even dynamic is the way to go...
Left by dave on Feb 12, 2007 8:29 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Personally there must be something better, i work with 2 - 3 million rows, in this case temp table looks useless, what if we can implement paging direct on the source table rather then creating a temp one.
Left by Murtaza Hussain on Mar 13, 2007 7:59 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
I am also finding such code but i want to split the records in multiple pages. Please have a look at it http://www.cure-t-pakherbs.org/welcome.php
Left by Donald on Jan 14, 2008 2:53 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
It's good approach for the beginners, can we solve without Identity ?
Left by VR.Palaniappan on Jan 29, 2008 8:41 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
hi All
there is problem with this sp .becuase if the record is deleted.then the returned records number are not same because records are fetched on ID.there is space for imporvement.
Left by Sameeullah Daris on Apr 09, 2008 11:39 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
To use current page and page size, I did like this:

Altered parameters to:

@pageSize INT,
@currentPage INT

And changed this line:

WHERE nID BETWEEN @nStartValue AND @nEndValue

to:

WHERE nID BETWEEN (@currentPage - 1) * @pageSize + 1 AND @currentPage * @pageSize

Please correct me if I'm doing it wrong. :)
/k
Left by kaba on Apr 27, 2008 10:08 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
You need to look at ROW_NUMBER() to do this effectively. Temp tables, cursors are to costly.

CREATE PROCEDURE myProc

@StartIndex INT
@MaxRecords INT

AS

SET @StartIndex = @StartIndex + 1

SELECT
first_name,
last_name,
middle_init
FROM
(SELECT
first_name,
last_name,
middle_init
ROW_NUMBER() OVER(ORDER BY last_name)
AS rownum
FROM
Users)
AS Users
WHERE
rownum between @StartIndex and (@StartIndex + @MaxRecords) -1


Left by Graham on Jun 04, 2008 12:27 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
SELECT
first_name,
last_name,
middle_init
FROM
(SELECT
first_name,
last_name,
middle_init
ROW_NUMBER() OVER(ORDER BY last_name)
AS rownum
FROM
Users)
AS Users
WHERE
rownum between @StartIndex and (@StartIndex + @MaxRecords) -1
Left by şişme bebek on Nov 20, 2008 1:23 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
sir,
I have a table that have a key column then how i applay custom pagging
Left by jitendra sharma on Nov 28, 2008 11:37 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Well, your query to good. But what if the UniqueID is deleted for some reason and if you try to fetch records between 11 to 20(Unique ID) and if 16, 17, 18 are deleted, then you will be able to fetch only 7 records. Am I right? Is there any other way we can fetch the records not based on UniqueID?
Left by Achutha Krishnan on Dec 10, 2008 10:09 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
What about joins ?!
Left by Yordan Georgiev on Jan 07, 2009 2:02 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Wow this really help improve the performance on my site.
Left by Joem on Jun 21, 2009 10:44 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
It would take more than one query to retrieve the whole thing, but it minimizes the amount of data that the client receives.
Left by mario oyunları on Sep 21, 2009 10:33 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Thanks for the Stored Procedure..i have implement the same in my website
Left by DeveloperJoint on Dec 15, 2009 5:45 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
very useful way to do.
Thanks for sharing this message.
Left by buy ipad cases on May 17, 2010 12:38 AM

# budget car rental

Requesting Gravatar...
This is definitely a blog worth following. Youve got a great deal to say about this subject, and so much knowledge. I think that you know how to make people listen to what you have to say, especially with an issue thats so important. Im glad to know this blog. Two big thumbs up, man!
Left by david gahan on May 29, 2010 3:17 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Two thumgs up here too!
Left by Fred on Jul 30, 2010 3:24 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
great job dude.. jou are a programming genius!
bubble
Left by bubble on Aug 05, 2010 2:32 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Ten the hastened steepest feelings pleasant few surprise property. An brother he do colonel against minutes uncivil. Can how elinor warmly mrs basket marked. Led raising expense yet demesne weather musical. Me mr what park next busy ever.
video converter
Left by jack on Sep 19, 2010 7:00 AM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Gr8 Stuff,
Thank u for Sharing ur knowladge.

Left by vipin on Jan 04, 2011 2:58 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Thank u for Sharing ur knowledge.

http://soft-engineering.blogspot.com/

Left by bipin on Jan 04, 2011 3:24 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Hi All, Thanks a lot, it worked for me. Nice Tip and nice trick.
Left by Mohd Hameed on Jun 20, 2011 1:02 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
i like comments thanks for sharing
Left by Royal herbal on Nov 10, 2011 5:29 PM

# re: Using SQL Server Stored Procedure for implementing Custom Paging

Requesting Gravatar...
Thanks for this article it's very good
SELECT TOP 10*
FROM Products
WHERE ProductId NOT IN
(SELECT TOP 20* FROM Products order by ProductId)
ORDER BY ProductId
But iam getting one error while executing
Like Error:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Left by dinesh on Nov 15, 2011 11:22 AM

Your comment:

 (will show your gravatar)