Corey Roth [MVP]

I have found a lot of people still don't know too much about this, so I thought I would post on it. Setting aside the reason why you are using stored procedures, if you run into a case where you need to have one with optional parameters that end up affecting a WHERE clause, here is how you do it. In the past, I've seen many examples where people end up creating strings with ad-hoc SQL and then executing them with an EXEC command. Although ad-hoc isn't considered a terrible thing necessarily any more by some groups, it is defintiely not as clean, and is more prone to run-time errors. The COALESCE operator works by returning the first argument that is not null.

So lets assume we had an optional parameter called @ProductId. You would construct your where clause like the following.

SELECT ProductName FROM Products
WHERE ProductId = COALESCE(@ProductId, ProductId)

So what happens here is that if @ProductId is null, the COALESCE returns ProductId, so you would have ProductId = ProductId, which always evaluates true. Since its true it does not affect the ultimate output of the SELECT statement. If @ProductId isn't null, it would return ProductName that matched that one particular id.

Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=398