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.

Kiran Hegde · Accepted Answer · 2014-06-26 12:26:56Z

9

You can do like this

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

answered Jun 26, 2014 at 12:26

Kiran Hegde

3,6811 gold badge18 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Damien_The_Unbeliever · Accepted Answer · 2014-06-26 12:34:15Z

3

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.

edited Jun 26, 2014 at 12:34

answered Jun 26, 2014 at 12:28

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Comments

Jesuraja · Accepted Answer · 2014-06-26 12:59:35Z

2

For separate assignment statement, you need to use SET

DECLARE @A INT
DECLARE @B INT
SET @A = (SELECT Col1 FROM Table1 WHERE ID = 1234)
SET @B = (SELECT Col2 FROM Table1 WHERE ID = 1234)

For single assignment statement, you can use

SELECT @A = Col1, @B = Col2 FROM Table1 WHERE ID = 1234

answered Jun 26, 2014 at 12:59

Jesuraja

3,8524 gold badges26 silver badges49 bronze badges

Comments

Adder · Accepted Answer · 2014-06-26 12:26:39Z

-2

You will want to use a cursor for iterating over the data set, even if it contains only one row. See http://stevestedman.com/2013/04/t-sql-a-simple-example-using-a-cursor/

answered Jun 26, 2014 at 12:26

Adder

5,8982 gold badges35 silver badges60 bronze badges

Collectives™ on Stack Overflow

Assign multiple Variables at once as result from Select Statement

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related