CODEWITHSUNDEEP
asp.netsql-serversearch
v s
v s

Reputation: 559

How to provide refine search for my ASP.NET website using SQL Server

We are developing a website using ASP.NET and sql server. We want to provide refine search that basically filters our search results for one or more specific criteria. We want to show all the refine search fields and count (similar to amazon website where several fields are displayed on the left of the search result with count)

something like
By Price
50 to 100 (10)
100 to 500 (50) ...

By Posted Date
Within 7 Days (40)
within 15 days (90)
Within 30 Days (150)

and so on ...

Our normal search occurs using several search criteria. We are using SQL Server and the stored procedure is using a paging technique and number of results per page.

So when you answer, please consider this information and provide a working sample link if you can.

Thanks a lot.

Upvotes: 0

Views: 873

Answers (1)

bhuvin
bhuvin

Reputation: 1402

This could only be done with minor modification in sql server procedure and your code.

Keep all the refining elements as the variables to the stored procedure. Pass the values of the refining parameters to the sql server for eg : if price is the refining attribute then make 2 variable @min_price and @max_price which will be passed to the stored procedure and are included in the where of the statement before paging and sorting takes place.
Edit :
You mean u need conditional parameters . In that case use ISNULL () For eg: You want products in a certain price range and products name with a certain Category i.e. Mobiles , Electronics . Now if this Category parameter is null then we need to avoid the comparision. In that case : 

SELECT * 
FROM Products p
WHERE 
p.Price BETWEEN @min_price AND @max_price
AND
p.Category = ISNULL(@category, p.Category)

Now in this case what happens is that the if the @category parameter is null then it replaces the comparision with Category itself i.e. Mobile = Mobile which will return true.

And if in case the query to be formed is too complex then dynamic sql queries are the only way out. But try to avoid these coz the execution plan is not saved for these and hence there is a performance hit.

Upvotes: 1

Related Questions