returning desired rows

[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

 

[gradley]

Something you might want to try is to have your stored procedure INSERT the entire potential selection (if it's not too large)into a temp table that has all the same fields with the addition of an IDENTITY column which in incrementally populated.

You could then control the starting record number (1,21,41) on the ASP page.

Pass the Starting Record number to the Stored Procedure and select the TOP 20 records from the temp table WHERE IDENTITY >= starting record number.

Hopefully this helps.

 

[nawrioj]

thanks for the respond . I will look into it