
/* 
Regular automatic (not forced) parameterization will not 
autoparameterize queries with IN lists.
As a trivial example, try the following. The first select will be 
autoparameterized, the second won't.
Forced parameterization does not suffer from this limitation.
*/

use tempdb
go

create table t(i int not null)
go

select * from t where i = 0 -- yes autoparam 
go

select * from t where i in (0, 1, 2) -- no autoparam 
go

/*
However, probably the best thing to do in order to have a single query
plan to handle a variable number of elements in a IN list is something 
along these lines.
When the number of elements in the list can vary widely, having a 
single pre-cooked query plan to handle all the possible combinations 
can save a ton of dynamic optimization overhead.
*/

create proc useunion @i1 int, @i2 int, @i3 int, @i4 int 
as 
begin  
select * from t, 
(select @i1 p 
 union all 
 select @i2 
 union all 
 select @i3 
 union all 
 select @i4) Q 
where i = p and p is not null 
end 
go

exec useunion 0, 1, 2, NULL
go

exec useunion 2,3,4,5
go




