T-SQL, updating more than one variable in a single select

Question

Is it possible to update more than one local variable in a single select?

Something like:

set
    @variableOne = avg(someColumn),
    @variableTwo = avg(otherColumn)
    from tblTable

It seems a bit wasteful to make two separate select operations for something as trivial as this task:

set @variableOne = ( select avg(someColumn) from tblTable )
set @variableTwo = ( select avg(otherColumn) from tblTable )

Answer 1

Something like this:

select @var1 = avg(someColumn), @var2 = avg(otherColumn) 
from theTable

Answer 2

how about

SELECT  @variableOne = avg(someColumn),  @variableTwo = avg(otherColumn) from tblTable

it works for me just fine.

Answer 3

You can use SELECT assignment to assign multiple variables. This code generates a single row of constants and assigns each to a variable.

SELECT
  @var1 = 1,
  @var2 = 'Zeus'

You can even query tables and do assignment that way:

SELECT
  @var1 = c.Column1,
  @var2 = c.Column2,
FROM
  Customers c
WHERE c.CustomerID = @CustomerID

Beware: This code operates like a while loop.

• If there are multiple rows, each row will be assigned to the variables and the last row will be the one left in there. If you didn't specify an ordering, you gave up control over which row will be the last row.
• If there are no rows, the variables will not be assigned to at all. The variables will not be set to null - they will remain unchanged. This is a key problem if the assignment is done in a loop (typically resulting in an infinite loop as the variables never change).

Prefer using SET assignment over SELECT assignment. Only use SELECT assignment when considering both scenarios above.