If you are on SQL 2008 or SQL 2008 R2 and on the latest SP and CU then you
can use OPTION( RECOMPILE ) to good effect because of embedded parameter
optimisation.
However, why don't you just do this instead...
declare @this_lookup char(3);
set @this_lookup = case when @test = 1 then 'aaa' when @test = 2 then 'bbb'
else null end
SELECT This, That From Table
WHERE This = @this_lookup
Tony.
"Marty McDonald" wrote in message
> I've heard that SQL Server can create a better execution plan if the
> query has unconditional logic as opposed to conditional logic. So
> where I might want to do this (conditional logic)...
> IF @Test = 1
> SELECT This, That From Table WHERE This = 'aaa'
> ELSE
> SELECT This, That From Table WHERE This = 'bbb'
>
> ...I will do this instead (unconditional logic)...
> SELECT This, That From Table WHERE This = 'aaa' AND @Test = 1
> UNION ALL
> SELECT This, That From Table WHERE This = 'bbb' AND @Test = 2
>
> Can SQL Server really handle the 2nd style better?
> Thanks
> Marty >> Stay informed about: Avoid conditional logic? 
FAQ
Search
Profile
Private Messages
Log in
