sql search query for multiple optional parameters

Question

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, '')

Answer 1

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) ...

Answer 2

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.

Answer 3

You could use COALESCE (or ISNULL) like so:

WHERE COALESCE(@var1, col1) = col1 
AND COALESCE(@var2, col2) = col2 
AND COALESCE(@var3, col3) = col3

Answer 4

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

Answer 5

I usually do this :P

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

Answer 6

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).

Answer 7

Hello there,

Thank you very very much!!!. it works for me. Thanks a lot