One Search box, 3 dropdownlists - one question.

[NicktheNewbie]

Ok,
Need to write a query that will be able to have 3 where clauses... but I have some questions.

3 drop down lists, any of which could have a null value
I need to query based upon the values (or lack thereof) in these fields.

WHERE Category = ISNULL(@Category, Category)
AND Location = ISNULL(@Location, Location)
AND ( FirstName LIKE @FirstSearch + '%'
OR LastName LIKE @LastSearch + '%' )

Is this the proper syntax to ignore a part of a where clause if it has a null value?

Also, I want to be able to allow for a null value in the search field:
if a user specifies a location and category, but doesn't enter anythinging into the searchbar for name, I still want all the records in the specified location and category to show up.

 

[SQLBill]

ISNULL means "if there is a NULL in the first expression, replace it with the second expression". Therefore:

Category = ISNULL(@Category, Category)

Means: If @Category is NULL, replace it with the value from Category. That probably won't work, since there is most likely more than one value in the column Category.

Usually ISNULL is used like: Category = ISNULL(@Category, 'Animals')
With 'Animals' being an actual Category value.

I believe you want to use the IS NULL (two words). Refer to the BOL for more information.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[ESquared]

SQLBill, the syntax

[tt]Category = ISNULL(@Category, Category)[/tt]

will work just fine when just @Category is Null, because the column value will compare as equal to itself, as though it was simply

[tt]Category = Category[/tt]

However, this won't work when Category is also Null because unless you have SET ANSI NULLS OFF they won't compare. And I don't recommend that anyone do that for various reasons such as ever wanting to put indexes on a view (materialized views).

NicktheNewbie, I suggest the following syntax:

WHERE
   (@Category IS NULL OR @Category = Category)
   AND (@Location IS NULL OR @Location = Location)
   AND (@FirstSearch IS NULL OR FirstName LIKE @FirstSearch + '%')
   AND (@LastSearch IS NULL OR LastName LIKE @LastSearch + '%')

This has the added benefit of letting the query engine not requiring a comparison to each row for any of the input variables which are Null.

For what it's worth, you might want to let the users do the wildcard adding themselves, so they can search for exact matches.

-------------------------------------
A sacrifice is harder when no one knows you've made it.