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.
Monday, October 09, 2006
Subscribe to:
Post Comments (Atom)
1 comment:
Des' zemljak,
ovo mi je trebalo :)
Post a Comment