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

Something like this:

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

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.

Related