This technique, although interesting, should be used sparingly as it affects performance. The optimizer will perform full table scans on all tables that have a smart filter on them. Therefore, I believe this technique is only acceptable if you're expecting your query to need full table scans anyway. Switch on Execution Plan viewing and inspect the execution plans for the SQL below. The SELECT with the smart filter will always read the entire Customers table, the SELECT you would normally arrive at doing it the conventional way (by building up your SQL according to the filter values that were not null or not the default value) will use a clustered index seek.

DECLARE @CustomerID nchar(5)
SET @CustomerID = 'AROUT'

SELECT *
FROM Customers
WHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)

SELECT *
FROM Customers
WHERE CustomerID = @CustomerID

Another way i commonly use is an extension on the above

WHERE
((@customerid is not null and customerid = @customerid) or @customerid is null)

If NULL is the default value used, the following construct can also be used:

WHERE (Column_Name = ISNULL(@Param_Name, Column_Name))

I experimented with this many years ago, if tables are small the benefits are good but on anything else the performance is apalling in terms of i/o and data cache flush. The more optional parameters there are the worse it gets. However, if the proc doesn't get called too often e.g. it's not in an oltp system and you need to avoid dynamic sql because of the security issues and views don't do much then I agree it's a good solution.

From my view in performance tuning developers usually find this and use it without testing on higher numbers of rows ( I won't say large table as it's all relative )  - so it works fine in test - in production tables get into double figure thousands of rows ( or worse ) and suddenly you're looking at a simple proc which is doing very high i/o to return ( usually ) 1 row. So basically what I'd say is this solution is good but it doesn't scale, so use with care.

I'm not having a pop here Alex btw .. but this illustrates that you should test with execution plans when developing code solutions and remember to scale up and out as part of testing.

I hope we don't get into another one of these silly dynamic sql arguments either!!

Alex - were you aware of another of Erland's excellent articles which discusses just this problem (in a good deal of detail)?

http://www.sommarskog.se/dyn-search.html

how is this technique all that clever? If anything, using

select *

from Customers

where customerid=isnull(@customerid,customerId)

is more efficient, easier to read and takes up less space.