Conditional Where Vs Dynamic query
<p>Most of us have used conditional where clause in our queries to get desired output for multiple parameter</p>

<pre><code>WHERE (@Variable1 IS NULL OR Column1 = @Variable1)
  AND (@Variable2 IS NULL OR Column2 = @Variable2)
  AND (@Variable3 IS NULL OR Column3 = @Variable3)
... and so on
</code></pre>

<p>This is a horrible way is the no of columns/parameters is large (from what I have observed more than 3 or 4 makes it horribly slow). and a solution to it is <strong>recompile</strong></p>

<p>Another way would be the use of dynamic query</p>

<pre><code>Set @query = 'Select column1, column2, column3 from table where '

Set @where = @Where + Case when @variable1 IS NOT NULL THEN 'AND Column1 = @Variable1' Else '' END

Set @where = @Where + Case when @variable2 IS NOT NULL THEN 'AND Column2 = @Variable2' Else '' END

... and so on
</code></pre>

<p>I am too lazy to type the entire codes for dynamic query :P</p>

<p>My questions are: in both the cases  </p>

<ol>
<li>Conditional where with recompile  </li>
<li>Dynamic query execution with <code>sql_execute</code>  </li>
</ol>

<p>The query plan will be generated again for each execution, So which one of these methods should be preferred for the ease of maintenance or any other factor. or do both of them provide their uses in different situations and I have completely misunderstood them? </p>