returning the desired rows 1

[nawrioj]

Hi I'm writing a site search code for asp and I have a problem with sql server stored procedure. I basically want to search the site and display 20 result perpage but I have no idea how to show result 1-20 on first page and result 21-40 on the next page and so on. Can somebody help me out here ??

the code below is basically what I have. I know how to return 20 rows using the @maxrows but how to display the next 20 result if any.

CREATE PROCEDURE dbo.selectSearchProducts

@searchterms as varchar(255),
@maxrows int = 0

AS

set nocount on
SET ROWCOUNT @maxrows

select
products.id,
products.categoryid,
products.name,
manufacturers.name as manufacturername,
manufacturers.description as manufacturerdescription,
products.longDescription, products.description,
products.image, products.largeimage,
products.minMSRP, products.maxMSRP,
products.rateitnow,
products.sortOrder, products.hidePriceOptions,
products.rating, products.numberofreviews,
products.metaTitle, products.metaKeywords,
products.metaDescription,
products.active

from products, manufacturers

where
((products.name like '%'+@searchterms+'%') OR (products.description like '%'+@searchterms+'%'))
and products.active = 1
and products.manufacturerid *= manufacturers.id

order by products.sortOrder asc
GO

Thanks.
nawrioj

 

[TJRTech]

This is actually more of an ASP question than a SQL question, though if new, I don't expect you would make that determination on your own.

What you should do is return all the records through your stored procedure and then use ADO and its client-side cursor capability to set a page size and absolutepage number, etc when paging through in your ASP page.

It is explained pretty well in the following Microsoft article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q202125

TJR

 

[JamesLean]

You need to do a search (either on this forum or Google) for "paging", which is what you are trying to do. There is loads of info out there on this subject as it is a very common task.

TJR has presented one solution but I personally would not choose to do it that way. Using that method you are returning all the data every time you access the page. Assuming you have a fair number of records (which is why you are doing paging in the first place) this could start seriously eating up resources and slowing down your page.

A better way is to handle the paging on the database side and only return the records you are going to display. This way you keep data transfer to a minimum.

--James

 

[nawrioj]

Thanks for the respond. I will look into both of the solutions.

nawrioj

 

[r937]

Assuming you have a fair number of records (which is why you are doing paging in the first place) this could start seriously eating up resources and slowing down your page.

excellent synopsis of the problem



star for you, james


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[TJRTech]

Oh, James, one thing, the solution that I presented is a Microsoft solution and it does in fact use a client side cursor, but the amount of data being returned will only be to the web server, not the client user, and the web server and the database server are typically pretty beefy and co-located.

Paging on the database side requires some sort of sorting as well, and if you don't have a good column with a clustered index than the database engine still ends up churning through an entire table in order to sort and do some sort of psuedo paging what with rowcount and a where clause that starts after some next in line record.