title: using dynamic criteria in SQL query
body: <p>If I have a query that runs a search with optional parameters, my usual approach is to pass in <code>NULL</code> for any unused criteria, and have a <code>WHERE</code> clause that looks like</p>
  
  <pre><code>WHERE (@Param IS NULL OR Field=@Param) AND (@Param2 IS NULL OR Field2=@Param2)
  </code></pre>
  
  <p>If I have a condition that maybe is a little more complex to evaluate, say a <code>LIKE</code> clause, is <code>SQL Server</code> going to evaluate the <code>IS NULL</code> and then short circuit the more complex condition? Is there something I could look at in the execution plan to find out if it is or not? Is this behavior dependent on the plan chosen by the optimizer, such that the answer might be different in my development and production environments (I've got limited access to see what's going on in production, so I'm hoping behaviors would be consistent).</p>