CODEWITHSUNDEEP
sqlsql-servert-sql
jandersson
jandersson

Reputation: 1629

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

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 )

Upvotes: 68

Views: 81607

Answers (4)

Cesar Murrieta
Cesar Murrieta

Reputation: 9

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

Upvotes: -2

Amy B
Amy B

Reputation: 110221

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.

Upvotes: 26

no_one
no_one

Reputation: 1850

how about 

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

it works for me just fine.

Upvotes: 1

Frederik Gheysels
Frederik Gheysels

Reputation: 56964

Something like this:

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

Upvotes: 134

Related Questions