Sql query for several optional parameters

Question

I am trying to write a query for an advanced search page in my document archiving system. I am trying to search for several optional parameters. I have about 5 parameters, which can be empty strings or search strings. I know that I did not need to check every line or empty and create a separate stored procedure for each combination.

Edit: Finished with:

ISNULL(COALESCE(@var, a.col), '') = ISNULL(a.col, '')

Answer 1

You can use COALESCE (or ISNULL) like this:

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

Answer 2

I usually do this: P

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

...

Answer 3

OR WHERE :

WHERE 
   (@var1 = '' OR col1 = @var1) AND
   (@var2 = '' OR col1 = @var2) AND
   (@var3 = '' OR col1 = @var3) ...

Answer 4

SQL , , ( 2005 ).

Answer 5

, , . SQL Server 2005 , , ( sniffing, " " ..).

, , procs . SQL , /, .

Answer 6

Better yet, make the parameter optional NULL, and then check in the WHERE clause, as is the case with an empty string ...