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

I can live with that.