Conditional Filtering: Using conditions in T-SQL WHERE clauses

Many of us have some time or another come across the scenario where we would like to use if statements in a where clause.

Here are 3 ways (that I know of) on how you can conditionally filter your where clause in T-SQL.

Using multiple queries seperated by IF Clauses

DECLARE @ContactID int 
SET @ContactID = 5

IF(@ContactID IS NULL)
BEGIN
 SELECT * FROM [AdventureWorks].[Person].[Contact]
END
ELSE
BEGIN
 SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = @ContactID
END

This must be one of the easiest ways to retrieve data depending on a variable and can also be the fastest way also.  Unfortunately, if you have a lot of variables, this could become a maintenance nightmare.  Imagine if you have to add another variable, the query will have to be repeated again, not to mention that if the query has to be amended, this needs to be done for every occurrence.

Using the Case expression

DECLARE @ContactID int
SET @ContactID = 5

SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = CASE WHEN @ContactID IS NULL THEN ContactID ELSE @ContactID END

Although not so straightforward, many developers opt to use this as it is much more readable and you only need one query.  I have used this for a number of years and found it extremely useful until I found out about the last option

Using boolean logic

DECLARE @ContactID int

SET @ContactID = NULL

SELECT * FROM [AdventureWorks].[Person].[Contact] 
WHERE @ContactID IS NULL OR ContactID = @ContactID

This method only uses Ands & Ors in order to get the result.  It takes a while to get used to but I highly recommend this method as it is much faster due to the conversion to simple boolean logics and it is also readable and easily maintained.

For more information visit http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx