5

I tried to set a value to variable in sub query but it doesn't work.

Here is my query:

declare @val1 int
declare @val2 int

select @val1 = sum(column1) 
,(select @val2 = (select sum(column2) from table2))
,(@val1+@val2)Result
from table 1

What I want to do is setting @val2 for sub query help me please I meant set in Sub query not separate select statement

user3916664
  • 97
  • 1
  • 4
  • 8

1 Answers1

6

Just use 3 separate selects:

select @val1 = sum(column1) from table1 
select @val2 = sum(column2) from table2
select (@val1+@val2) as Result

Or you can also write 2 selects:

 select @val1 = sum(column1), 
        @val2 = (select SUM(column2) from table2)
 from table1
 select (@val1 + @val2) Result

But not just 1 select:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

If you need to accomplish all in one select and return a recordset, do not use variables, do it like this:

SELECT sum1 + sum2 FROM (
    select sum(column1) as sum1, 
           (select SUM(column2) from table2) as sum2
    from table1
) subquery
Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
  • my code above is just for example for easy to understand my problem but accually my query for assign to val2 is very long so i have to use it for many time – user3916664 Oct 04 '14 at 07:05
  • I got this error when trying to use your 2nd method I got this erer : Msg 141, Level 15, State 1, Line 7 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. – user3916664 Oct 04 '14 at 07:15
  • The second method works well, you try to combine the 2 selects into one and that is the problem. If you want to process all in one select and return a recordset, do not use variables. – Vojtěch Dohnal Oct 04 '14 at 07:18