>>>>>>>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.
>>
>>Sorry Walter.
>>
>>Dynamic SQL should always be built with parameters. The parameters are what protect against SQL Injection.
>
>Mike, Note that I made a distinction between parameterized queries and "pure" dynamic SQL where the values are poked into the SQL command. The latter technique is susceptible to SQL injection.

OK. I missed that distinction. I'd call parameterized dynamic sql "pure" as in fresh and clean. Anything else is dirty dynamic sql. Maybe the terms should be non-parameterized dynamic sql versus parameterized dynamic sql.