Local variable assignment in SQL subquery

Question

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.

My query is as follows

declare @temp1 varchar(200)
declare @temp2 varchar(200)

select case when cnt>0 then 'RouteA' else 'RouteB' end as Route from
( select 
        (
          (select case when (req.Avg > 10) then 1 else 0 end from
            (select count(val) as Avg from T1) req) +

          (select case when (req.Avg > 10) then 1 else 0 end from
            (select count(val) as Avg from T2) req)                               
       ) as cnt) t

What I need to do is assign the value of the following subquery to @temp1:

(select case when (req.Avg > 10) then 1 else 0 end from
(select count(val) as Avg from T1) req)

and this subquery to temp2:

(select case when (req.Avg > 10) then 1 else 0 end from
(select count(val) as Avg from T2) req)

I've tried doing this a number of ways but keep getting syntax errors.

Any help would be appreciated!

Thank you, Charlie

Answer 1

You can't mix variable assignment and a result set in the same select. Your SQL also has other problems. I dont think you can select from the result of a count().

Why dont you assign your variables, then do the select on them if you need to?

IF (select count(val) as Avg from T1) > 10 
  SET @temp1 = 1
ELSE
  SET @temp1 = 0

You could also do this in a select if you need to

SELECT @temp1 = CASE WHEN (select count(val) as Avg from T1) > 10 THEN 1 ELSE 0 END