title: Local variable assignment in SQL subquery
body: <p>I'm trying to execute a SQL query containing multiple subqueries and then assign some parts of it to a local variable. Unfortunately I'm having issues getting the syntax correct.  </p>
  
  <p>My query is as follows</p>
  
  <pre><code>declare @temp1 varchar(200)
  declare @temp2 varchar(200)
  
  select case when cnt&gt;0 then 'RouteA' else 'RouteB' end as Route from
  ( select 
          (
            (select case when (req.Avg &gt; 10) then 1 else 0 end from
              (select count(val) as Avg from T1) req) +
  
            (select case when (req.Avg &gt; 10) then 1 else 0 end from
              (select count(val) as Avg from T2) req)                               
         ) as cnt) t
  </code></pre>
  
  <p>What I need to do is assign the value of the following subquery to @temp1:</p>
  
  <pre><code>(select case when (req.Avg &gt; 10) then 1 else 0 end from
  (select count(val) as Avg from T1) req)
  </code></pre>
  
  <p>and this subquery to temp2:</p>
  
  <pre><code>(select case when (req.Avg &gt; 10) then 1 else 0 end from
  (select count(val) as Avg from T2) req)
  </code></pre>
  
  <p>I've tried doing this a number of ways but keep getting syntax errors.</p>
  
  <p>Any help would be appreciated!</p>
  
  <p>Thank you,
  Charlie</p>