current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 5 down vote favorite
3

I'm trying to write a query for an advanced search page on my document archiving system. I'm attempting to search by multiple optional parameters. I have about 5 parameters that could be empty strings or search strings. I know I shouldn't have to check for each as a string or empty and create a separate stored procedure for each combination.

Edit: Ended up using:

ISNULL(COALESCE(@var, a.col), '') = ISNULL(a.col, '')
share|improve this question
    
See also: stackoverflow.com/questions/532468/… –  Marc Gravell♦ Feb 12 '09 at 15:07

6 Answers 6

up vote 8 down vote accepted

You could use COALESCE (or ISNULL) like so:

WHERE COALESCE(@var1, col1) = col1 
AND COALESCE(@var2, col2) = col2 
AND COALESCE(@var3, col3) = col3
share|improve this answer
1  
This solution will not work if the column value is NULL because you cannot NULL cannot be tested that way. If the value is NULL, the row will be filtered out. This is not what you want. –  G Mastros Dec 2 '08 at 21:06
    
Function call in where condition will slow down the performance. Instead of using coalesce function call this, where (@var1 is null or col1=@var1) –  Yogesh Bhadauirya Jul 18 '11 at 7:07
up vote 6 down vote

I usually do this :P

WHERE (@var1 IS NULL OR col1 = @var1)
AND (@var2 IS NULL OR col2 = @var2)

...

share|improve this answer
up vote 1 down vote

You can put OR's in your WHERE clause like so:

WHERE 
   (@var1 = '' OR col1 = @var1) AND
   (@var2 = '' OR col1 = @var2) AND
   (@var3 = '' OR col1 = @var3) ...
share|improve this answer
    
While this solution will work, it's incredibly expensive. Don't use OR... instead use the ISNULL (example above). –  Timothy Khouri Dec 2 '08 at 13:56
2  
This solution will work in ALL cases. The IsNull/Coalesce solution will only work under controlled circumstances. When you use Coalesce, you are still testing for a column to EQUAL a value. If the value in the column is NULL, it will not be EQUAL and the row will NOT be returned. –  G Mastros Dec 2 '08 at 21:04
up vote 1 down vote

An alternative is to dynamically built the SQL in the Stored Procedure, this produces the best possible plan for the query and a plan will be created and used anyway (in 2005 and above).

share|improve this answer
up vote 0 down vote

You can pass optional parameters to a stored procedure but the optimizer will build a plan based on the specific calls you make to that proc. There are some tricks in SQL Server 2005 and later to avoid this (parameter sniffing, 'with no compile' hints, etc.)

Even with that, tho, I prefer to build a view with the core of the query and then use that view in several procs with the specific parameters. That allows SQL to optimize as it wants/should and I still get to consolidate the query specifics.

share|improve this answer
up vote 0 down vote

Even better is to make the parameter optional NULL and then test in the WHERE clause just like the empty string case...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.