<< Button Click Events not firing ? - ASP.NET | Home

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 !!!

posted @ Monday, April 25, 2005 8:00 AM

Print

Comments on this entry:

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

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

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

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

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

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

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

Left by M. Clark at 10/4/2005 9:56 AM
Gravatar
Oooh, cheers for that.

It looks much easier than the above dynamic-sproc :).

# queries

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

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

Left by tom at 7/28/2006 6:10 AM
Gravatar
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

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

Left by Kaladharan at 11/20/2006 11:07 AM
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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Left by Graham at 6/4/2008 12:27 AM
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


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

Left by şişme bebek at 11/20/2008 1:23 PM
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

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

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

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

Left by Achutha Krishnan at 12/10/2008 10:09 AM
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?

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

Left by Yordan Georgiev at 1/7/2009 2:02 AM
Gravatar
What about joins ?!

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

Left by Joem at 6/21/2009 10:44 PM
Gravatar
Wow this really help improve the performance on my site.

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

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

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

Left by DeveloperJoint at 12/15/2009 5:45 AM
Gravatar
Thanks for the Stored Procedure..i have implement the same in my website

Your comment:



 (will not be displayed)


 
 
 
 
 

Live Comment Preview:

 
«March»
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910






ASP.NET

Promote Your Page Too

About
Twitter











Archives
Post Categories
Blogs I read
Syndication:

RSS ATOM