A question from a reader (thank you, Price, for sending it in).  This is a follow up to my prior post on Optional parameters http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/22/conor-vs-optional-parameters.aspx.

The question is whether using col LIKE @p where @p is ‘%’ in the case of no parameter is an acceptable workaround to the problems listed in the original post (basically index matching). The original formulation (@p is null or col = @p) was bad because it generally precluded index matching in existing versions of SQL Server. 

While I think that the LIKE approach is a reasonable suggestion, it is not really a valid solution in all cases.  Specifically, it does not work for columns that are not strings.  It also won’t work if the string column is nullable as the pattern can’t query for NULLs.

use tempdb

create table str1(col1 int, col2 nvarchar(20))
insert into str1 (col1, col2) values (1, NULL)

select * from str1 where col2 LIKE '%'
go
declare @p nvarchar(20)
set @p = NULL
select * from str1 where col2 LIKE @p
go

There’s the repro you can try.  In both cases, no rows will be returned. 

It likely will match the index and work if the column is a string and the column is defined as NOT NULL.  So, in that subset of cases, you might consider it.  However, note that the optimizer may not sniff the parameter value during compilation and even then may assume that the operation should restrict at least _some_ rows (as it is hard to believe people would purposefully write overly complex queries, at least from the perspective of the people implementing the engine).  So, you may or may not get acceptable query plans with this approach and you should really proceed with caution.  I think that the previously published workarounds (dynamic sql) may be better options until such time as we ship a version of SQL Server that makes this easier for our users.

Hope that helps!

Conor