>>>>I would recommend dynamic SQL in any case. You'd only optimize the parts of the where clause that need it.
>>>
>>>What do you mean by "You'd only optimize the parts of the where clause that need it"?
>>
>>If you create a where like
>>
>> (@MinDate is null OR @MinDate < = Date) and
>> (@MaxDate is null OR @MaxDate > = Date) and
>> (@MinAmount is null OR @MinAmount > = Amount) and
>>
>>SQL is doing a lot of extra work internally. If you construct the query dynamically so that in the case where @MinAmount is not passed you execute
>>
>> (@MinDate is null OR @MinDate < = Date) and
>> (@MaxDate is null OR @MaxDate > = Date)
>>
>>The optimizer will not waste time even thinking about MinAmount. :)
>
>Correct me if I'm wrong, but that is (I believe) basically what the RECOMPILE will do. I'm not saying one is better than the other - just curious if there are situations where dynamic SQL is going to yield a better execution plan than a RECOMPILE.

I'd say so. RECOMPILE takes a lot of overhead. As each dynamic SQL is executed, the plan will be cached. RECOMPILE would recreate the plan per execution.