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 could use COALESCE (or ISNULL) like so:

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