>>>>>>>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.
>>
>>Actually there is no difference. If you have a "pure" dynamic SQL command where the values are poked into the SQL command, it will do exactly the same. For each different value it will do a recompile.
>>
>>SQL server will look at the SQL command, takes a CRC and stores that in the plan cache. If a SQL command with the same CRC is executed next it will use the same plan. With "pure" dynamic SQL it will be different when values change.
>>
>>RECOMPILE is necessary for certain queries. As values change and the selectivity of the index values change, the previously established execution plan can prove entirely inefficient for the new parameter values. Then you need a RECOMPILE to make sure that for each execution the best execution plan is used.
>>
>>BTW, I've found no measurable delays when RECOMPILE for most queries. Only in large queries with large plans and in cases where queries are executed in tight loops (often bad design), it might matter. In all other case it just does not matter.
>
>Let's dismiss the non-parameterized dynamic sql. I am trying to outline a specific scenario. Please proceed with parameterized ad-hoc / dynamic sql versus RECOMPILE. I believe if there are a lot of plans in the cache recompile will cause thrashing of the caching. :)
>
>Let's try with two parameters.
>
>1 A query is executed passing parameter 1 and 2 together with RECOMPILE.
>2 A plan is produced and cached.
>3 The same query is executed with only 1 parameter with RECOMPILE
>4 The previous plan is discarded from the cache.
>5 A new plan is cached.
>6 The first query is run again passing both parameters with RECOMPILE again.
>7 the plan is discarded
>8 a new plan is cached.
>9 the second query is executed with recompile
>10 the plan is discarded
>11 the new plan is cached.
>Cached plans are basically being lost every time.
>
>Caching plans is supposed to be a good thing. The other option is ad-hoc parameterized sql.
>1 A query is executed with both parameters
>2 a plan is produced and cached
>3 The second query with only 1 parameter executes
>4 It produces a plan and caches it.
>5 The first query is re-run.
>6 The plan is reused.
>7 The second query is re-run
>8 it reuses it's plan.
>
>This way was already 1 step ahead by step 5 and three steps ahead by step 8.

I'm not quite sure what you mean running the same query with a different number of parameters. Its not relevant in the discussion as the query is setup to accept two parameters. You cannot run the same query with only one, else it is not the same query and would therefore have two different execution plans.

Caching execution plans is often beneficial, but not always so. Certain types of plans making use of high selectivity of a particular index value might be (because of the skewness of the distribution of values in the index) low selective for a different parameter value. This might cause the query to run extremely fast for one set of parameters values, but exceptionally slow for another. In the latter case, the other values need a different execution plan than the first set of values. In this case a RECOMPILE is beneficial.

As for the number of steps you outline above: Its not about the number of steps, but about how much time the overall process costs.
I'd argue that in most cases, it would not make any significant difference. Further, you might benefit for some queries that are executed more efficiently because the plan changes with a different set of parameter values.

That is not to say that I'm advocating RECOMPILE as a standard to your code, I'd rather identify those cases where it does matter and add the RECOMPILE when necessary.

Walter,