title: Select with subquery avoid running more than once?
body: <p>I have a query like this</p>
  
  <pre><code>Select 
       Col_A
      ,Col_B
      ,Col_C
      ,CASE 
          WHEN (SELECT ...{a very complicated query}...) IS NOT NULL THEN 1
          ELSE 0
       END CASE AS Col_D
  FROM
      MyTable
  </code></pre>
  
  <p>The subquery that produces Col_D returns an integer when it is not null.</p>
  
  <p>I need to show the BIT that Col_D is, but I also need to show the INT that it returns. Normally, I would just rewrite the subquery and call it Col_E, but given it's complexity, I don't really want to run it twice. Ideally, I would have:</p>
  
  <pre><code>Select
       Col_A
      ,Col_B
      ,Col_C
      ,(SELECT ...{a very complicated query}...) AS Col_E
      ,CASE 
          WHEN Col_E IS NOT NULL THEN 1
          ELSE 0
       END CASE AS Col_D
  FROM
      MyTable
  </code></pre>
  
  <p>Do I have any options? (MS SQL 2008)</p>
  
  <p>EDIT:
  Sorry - I should have mentioned that the complicated query includes a where clause based on my columns i.e.</p>
  
  <pre><code>SELECT ...{a very complicated query}... WHERE X = Col_A AND Y = Col_B
  </code></pre>