title: Force SQL Server to go through desired execution plan
body: <p>SQL Server Version : 2012 </p>
  
  <p>The stored procedure accepts a parameter <code>@personID</code> </p>
  
  <p>The procedure is written this way:  </p>
  
  <pre><code>select [some columns] 
  from 
  T1 join T2
  on T1.id=T2.id
  where
  [condition 1]
  and 
  [condition 2]
  and 
  (
    @personid=10
    or 
    @personid&lt;&gt;10 and T1.addressID in 
                      ( select T3.addressID from 
                        T3 join T4 on T3.uid=T4.uid 
                        where [some conditions]
                      )
  )
  </code></pre>
  
  <p>The Problem is :<br>
  SQL Server is executing the second half i.e <code>@personid&lt;&gt;10</code> and T1.* even when <code>personid=10</code>, which is causing unwanted reads and cpu time.</p>
  
  <p>Is there any way like if/else, case statements to restrict it to not check the second half if first condition is met.</p>
  
  <blockquote>
    <p>EDIT: </p>
  </blockquote>
  
  <p>I liked the answer of Vojtěch Dohnal (i.e using Option (Recompile) ) as well as answer of Rigerta Demiri (i.e use of if/else or separate stored procedures). I would say, that's a tie breaker.</p>
  
  <p><strong>To all who are finding solution to similar problem:</strong><br>
  My suggestion is, if the Query's compile time is very short and is also not one of the frequently executed statements/procedures on production, then one should surely go with <strong>Option (Recompile)</strong>.    </p>
  
  <p>If it doesn't meet the aforesaid conditions then you should go with either if/else method or using two separate stored procedures.  </p>
  
  <p>Considering the fact that the select statement in my case has so many nested queries, its compile time isn't short and it is also one of the most frequently used procedures in production, so I have gone with creating two separate SPs, one for parameter match and one for otherwise.</p>