IsNull != Coalesce

One of the lesser used Sql functions is Coalesce.  Coalesce replaces any given value with another given value if the first value is null.

IsNull does something similar, however there is a bit of differences.

• IsNull performs a tad better.
• IsNull will truncate values.
• Coalesce looks cleaner in scripts. (if the developer knows what coalesce does)
• Coalesce is more reliable because it doesn't truncate values.

One scenario for using Coalesce is to toggle between selecting all values and a single value in a query.

For example

WHERE COALESCE(@ProductID,ProductID) = ProductID

would select all products if @ProductID is null.

So what happens with this statement if the Product Name column is 50 characters and the @ProductName variable is 25?

WHERE IsNull(@ProductName, ProductName) = ProductName

If you have a product with 26 characters or more, it will not be returned using IsNull.

For multi-value parameters, one option is to use a split utility to parse comma-delimited parameters and use the following statement:

WHERE (@ProductIDs is null or ProductID in in (dbo.fn_utilsplit(@ProductIDs,'''))