Thread: add Categories to getProducts

Does anybody know of a way to modify the getProducts stored procedure so that a search by Entity name will return all the products that are mapped to that Entity?

I feel like this must be a pretty common operation but wasn't able to find anything searching this forum.

Thanks for your help!

this will probably start you off

http://forums.aspdotnetstorefront.co...ad.php?t=16622

Thanks Mike, this definitely is helpful.

I'd like to find a solution that puts most of the work on the getproducts since that's the main procedure that generates search results. I'm just wondering if anybody out there has tweaked it to allow users to search for products by a category.

T

for this purpose i would rather just write a new sp. the get products procedure is used in multiple places over the site, and writing something for this purpose is pretty straight forward and easy. im not on my work computer so i can't guarantee the syntax here, but it would be something like this:

Code:

CREATE PROCEDURE dbo.custom_getProductsByEntity
@EntityID int

SET NOCOUNT ON
BEGIN
SELECT * FROM Product INNER JOIN ProductCategory ON Product.ProductID=ProductCategory.ProductID WHERE ProductCategory.CategoryID=@EntityID

You can already specify the category for GetProducts - just take a look in one of the entity xmlpackages......


TTFN

BFG

The searchadv.aspx will also do. The page has already the feature to search for a particular product mapped to a certain category, manufacturer, department, distributor, etc.

--> miguelcrush - I might wind up doing that after all is said and done. Unfortunately I'd have to go back and change a significant amount of my page logic if my products had to display from a different node set depending on wether the user searched by a product name or by a product category. This is the idea behind doing all the work in a single sproc that returns a single XML tree for the package to transform.

--> BFG 9000 - I see how you can specify a category ID through the @categoryID parameter, however, that only takes an category number which the user would not know to search for. Have you made a modification that gets around this? I'm definitely interested!

--> Jao - Again, that page looks like it requests CatID, so I wonder how you would get @categoryID to accept a value through through @searchstr.

My train of thought right now is to add the parameter @categoryName to the sproc and set it to the search term. Then I'd add a few new lines in the select statements that mimic how @categoryID is handled except now I'd be looking for a match in Name column of the Category table, for example, WHERE (c.Name = @categoryName or @categoryName is null or @categorycount = 0).

No idea what results I'm going to get or if it'll work at all. I'll definitely post my solution if I can get it to work the way I want.

By the way, I really appreciate everybody's insight on this matter.

got it!

It turned out to be an even simpler modification than I had anticipated.

In order to get the search engine to return searches by category name all you have to do is edit aspdnsf_GetProdcuts like so:

(the lines I added are in bold)

1) Join in the Category table underneith the first INSERT @productfilter (for me, line 175)

left join ProductLocaleSetting pl with (nolock) on p.ProductID = pl.ProductID
left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID
left join ProductAffiliate pa with (nolock) on p.ProductID = pa.ProductID
left join Category c with (nolock) on pc.CategoryID = c.CategoryID

2) Tell the query to look for string matches in the name column of the Category table (for me, line 197)

or patindex(@searchstr, isnull(p.name, '')) > 0
or patindex(@searchstr, isnull(c.name, '')) > 0
or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0

3) Repeat the process in the second INSERT @productfilter section. This is the ELSE part of the first query, but the changes are identical to steps 1 and 2. I made mine on lines 244 and 255 repectively.


It works just fine for me so I hope this solves it. I'll post any problems that I might have.

Good luck!

It worked for me as well.

Thanks