title: Short circuit CASE/iif
body: <p>I created a table valued function, which takes an optional parameter, which should determine whether a rather lengthy calculation (<code>PERCENTILE_CONT</code> in my case) should be run.</p>
  <p>For this purpose I use a construct like this in my select statement</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT CASE @my_param 
         WHEN 1 THEN NULL 
         ELSE --run lengthy calc
         END
  </code></pre>
  <p>However, looking at the execution plan it seems that the lengthy calculation is triggered irrespective of how I set the parameter.</p>
  <p>The problem can be reproduced with the following lines:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE #temp(a int)
  INSERT INTO #temp Values(1)
  DECLARE @i INT = 1;
  SELECT CASE WHEN @i=1 THEN 1
         ELSE PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a) OVER ()
         END
    FROM #temp
  </code></pre>
  <p>If you look at the execution plan, you will see that the <code>PERCENTILE_CONT</code> function is included irrespective of the value of <code>@i</code>. Since this very function slows down my query by a bit, I was wondering how to best circumvent that?</p>
  <p>I tried to add an <code>IF</code> construct paired with an <code>UPDATE</code> statement in my function and while this avoids running the <code>PERCENTILE_CONT</code> function when not needed, it slows down the function when I want to include the <code>PERCENTILE_CONT</code> function. So what other options do I have to run <code>PERCENTILE_CONT</code> only in needed cases?</p>
  <p>My median will eventually be calculated for several subgroups, thus I decided to go for <code>PERCENTILE_CONT</code> in the first place, knowing that it is not the best performance wise, but is very readable. Hence, while I appreciate hints of how to calculate the median in a more efficient way, I would also be curious to learn how to avoid or short circuit the calculation without the need to fall back to an ordinary <code>if</code> clause.</p>