 <p>You're building a <a href="http://www.sommarskog.se/dyn-search-2008.html" rel="nofollow">dynamic search condition</a>. By forcing one single statement to cover <em>both</em> cases you are cutting the optimizer options. The generated plan has to work in <em>both</em> cases when <code>@seconds</code> is null and when is not null. You'll be much better using two separate statement:</p> <pre><code>IF @SECOND IS NULL THEN SELECT * FROM BANK_DETAIL WHERE X = @FIRST ORDER BY X ELSE SELECT * FROM BANK_DETAIL WHERE X &gt;= @FIRST AND X &lt;= @SECOND ORDER BY X </code></pre> <p>You intuition to 'simplify' into one single statement is leading you down the wrong path. The result is less text, but much more execution time due to suboptimal plans. The article linked at the beginning of my response goes into great detail on this topic.</p>