An ASP.NET Blog
I work for Microsoft and help people and businesses make better use of technolgy to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my employer.

Using SQL Server Stored Procedure for implementing Custom Paging

Monday, April 25, 2005 8:00 AM
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 !!!

Feedback

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

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
7/4/2005 7:19 AM | P.Suresh

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

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... 9/2/2005 1:19 PM | M. Clark

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

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. 9/23/2005 10:58 AM | J.Gregory

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

Oooh, cheers for that.

It looks much easier than the above dynamic-sproc :). 10/4/2005 9:56 AM | M. Clark

# queries

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
10/27/2005 7:11 AM | bhuvanesh

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

hi,
your article is good. I have one question though, how can you know how many pages can be shown. every time when you use the stored procedure you get 10 records back, but how many pages are there with 10 records on each page 7/28/2006 6:10 AM | tom

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

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


11/20/2006 11:07 AM | Kaladharan

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

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 11/28/2006 8:41 PM | jaime_gonz

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

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... 2/12/2007 8:29 PM | dave

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

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. 3/13/2007 7:59 AM | Murtaza Hussain

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

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 1/14/2008 2:53 PM | Donald

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

It's good approach for the beginners, can we solve without Identity ? 1/29/2008 8:41 PM | VR.Palaniappan

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

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. 4/9/2008 11:39 PM | Sameeullah Daris

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

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 4/27/2008 10:08 PM | kaba

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

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


6/4/2008 12:27 AM | Graham

Post a comment





 

Please add 1 and 1 and type the answer here: