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