The partitioned view (pvF_Call_Level1) unions several tables that have ClientID as the column with the check constraint.

The queries:

Without variable--works. Query plan shows 1 table accessed

select dateID, count(*)

from dw.dbo.pvF_Call_Level1

where dateID between 20080508 and 20080509

and ClientID = 4

group by DateID

With variable--doesn't work. Query plan shows all tables accessed

declare @ClientID integer;

set @ClientID = 4;

select dateID, count(*)

from dw.dbo.pvF_Call_Level1

where dateID between 20080508 and 20080509

and ClientID = @ClientID

group by DateID