>>>>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.

If parameter sniffing is in place (Directly using the parameters in the query), there should not be a difference in the execution plan (Except maybe of the fluf, you mentioned before that is added to).

IMO, one big plus for using parameterized queries rather than pure dynamic SQL is the protection agains SQL injection. Therefore I tend do everything through parameters, and if neccesary I will add " RECOMPILE" for cases where it might matter.