title: Using the result of a subquery in a CASE expression with T-SQL
body: <p>I'm writing a query with some CASE expressions and it outputs helper-data columns which help me determine whether or not a specific action is required. I would like to know if I can somehow use the result of a subquery as the output without having to perform the same query twice (between <code>WHEN (subquery) THEN</code> and as the result after <code>THEN</code>)</p>
  
  <p>The dummy code below describes what I'm after. Can this be done? I'm querying a MS2005 SQL database.</p>
  
  <pre><code>SELECT   'Hello StackOverflow'
          ,'Thanks for reading this question'
          ,CASE
              WHEN 
                  (
                      SELECT count(*)
                      FROM    sometable
                      WHERE   condition = 1
                      AND     somethingelse = 'value'
                  ) &gt; 0 THEN
                      -- run the query again to get the number of rows
                      (
                          SELECT count(*)
                          FROM    sometable
                          WHERE   condition = 1
                          AND     somethingelse = 'value'
                      )
              ELSE 0
           END
  
  SELECT   'Hello StackOverflow'
          ,'Thanks for reading this question'
          ,CASE
              WHEN 
                  (
                      SELECT count(*)
                      FROM    sometable
                      WHERE   condition = 1
                      AND     somethingelse = 'value'
                  ) AS subqry_count &gt; 0 THEN
                      -- use the subqry_count, which fails... "Incorrect syntax near the keyword 'AS'"
                      subqry_count
              ELSE 0
           END
  </code></pre>