There are several common methods for using dynamic search conditions (a.k.a. optional parameters) in SQL. By dynamic search conditions, I am talking about passing through several criteria to a stored procedure or similar, but only filtering by the criteria that have values specified (i.e. are not null).

To illustrate this, here is one common method:

SELECT *
FROM Orders
WHERE
 (@productName IS NULL OR Orders.ProductName LIKE @productName + '%')
 AND
 (@orderDate IS NULL OR Orders.DatePlaced = @orderDate)

This will let you filter the orders by product name, the date placed, or return all results unfiltered. Unfortunately SQL Server can’t figure out how to use an index for this situation, so you end up with full table scans and the subsequent performance hit (which may or may not be a problem for you depending on things like your table size).

There are several variations on this theme, including using COALESCE (note retraction at top of that post), or inverting the conditions (using (@productName IS NOT NULL AND Orders.ProductName=@productName) OR ...) which can help by calculating the filter earlier and potentially allowing index scans. Another option is to use dynamic SQL to build up the appropriate condition, but that way is fraught with its own perils.

Erland Sommarskog’s article, Dynamic Search Conditions in T-SQL, covers everything you ever wanted to know about this topic, but were afraid to ask. It really is a must read for anyone working with SQL (unless you’ve given up on the whole structured query language thing and are holding out for LINQ-to-SQL ;)).