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

jandersson_56506 0
Viewed 66898

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 )
tsql sql-server sql
4 Answers

Something like this:

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

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.

user_81550

how about 

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

it works for me just fine.

no_one_356620 
SELECT DISTINCT 
@Var1 = Column1,
@Var2 = Column2
FROM MyTable 
WHERE Columnx = @Parm1
user_125145750
Related