Ever have to write sql where you optionally have criteria... like a multivariable search form? You know like looking up a person by first name, last name, address etc where you may have one or more of those options filled in by the user.

I found an interesting blog that talks about using COALESCE. Instead of building the where criteria iteratively (i.e. strWhere = strWhere + "lastname = ? " ... strWhere = strWhere + firstname = ?") you can use COALESCE. It is an interesting read. Basically COALESCE returns the first non null value passed in.

I summarize here (you can get the details by following the link). The magic is that the code basically makes fieldname = fieldname when there is no parameter given for the search. It has one problem: using the code below as an example... what happens if both lastname and @lastname are null. This resolves to '' = NULL which is false, breaking the query. I stand corrected (by Scott Elkin): If both lastname and @lastname are null the third argument to COALESCE is returned.

SELECT * FROM Customers
WHERE
  ISNULL(firstname,'') = COALESCE(@firstname,firstname, '')
  AND isnull(lastname,'') = COALESCE(@lastname,lastname, '')
  AND isnull(phone,'') = COALESCE(@phone,phone, '')
  AND isnull(email,'') like COALESCE(@email,email, '')
  AND isnull(address,'') like COALESCE(@address,address, '')

http://www.scottelkin.com/archive/2005/05/06/558.aspx