Assign multiple Variables at once as result from Select Statement

Question

Due to runtime I want to asign the columns of a single-row-resultset to multiple variables at once in a SQL-Server stored Procedure. I want to replace the last 2 lines of the following code:

Declare @A int
Declare @B int
Select @A = (Select Col1 From Table1 Where ID=1234)
Select @B = (Select Col2 From Table1 Where ID=1234)

Since in this version the program would search twice for the ID=1234 i want to do something like

Select @A, @B = (Select Col1, Col2 From Table1 Where ID=1234)

But i can't figure out the correct syntax for this.

Answer 1

You can do like this

Select @A =Col1,@B=Col2  From Table1 Where ID=1234

Answer 2

Just comma separate them and have the assignments directly in the SELECT rather than using a subquery:

Select @A = Col1,@B =  Col2 From Table1 Where ID=1234

See select @local_variable:

SELECT { @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression } [ ,...n ] [ ; ]

Where the [,...n] means you can repeat the preceding part as many times as you want to, separating each repeat with a comma.