Monday, October 09, 2006

Using ISNULL() for parametrized filtering

Alex Grinberg over at SQL Server central wrote an article named "Self Eliminated Parameters" on how to avoid building dynamic SQL in queries that filter by more than one optional parameter. The technique boils down to the following use:

(COLUMN_NAME = @PARAM_NAME OR @PARAM_NAME = DEFAULT_VALUE)

and he shows the example for Northwind database:

(Products.ProductName = @prodname OR @prodname Is Null)

I have been using this technique for years but with a twist: instead of doing my own OR operation I leverage T-SQL ISNULL() command. Like this:

Products.ProductName = ISNULL(@prodname, Products.ProductName)

However, not that I'm looking at it I'm wondering if it's not too "cute". In any case from my tests the performance is on par or a tiny bit better than with OR condition.

1 comment:

Mika said...

Des' zemljak,
ovo mi je trebalo :)