Setting two scalar variables in one SELECT statement?

SqlSql ServerTsql

Sql Problem Overview


I want to do this:

Declare @a int;
Declare @b int;

SET @a,@b = (SELECT StartNum,EndNum FROM Users Where UserId = '1223')

PRINT @a
PRINT @b

But this is invalid syntax. How do I set multiple scalar variables in one select statement? I can do:

Declare @a int;
Declare @b int;

SET @a = (SELECT StartNum FROM Users Where UserId = '1223')
SET @b = (SELECT EndNum FROM Users Where UserId = '1223')

PRINT @a
PRINT @b

But this will take twice as long. What is the fastest way?

Sql Solutions


Solution 1 - Sql

DECLARE @a int;
DECLARE @b int;

SELECT @a = StartNum, @b = EndNum 
FROM Users 
WHERE UserId = '1223'

Solution 2 - Sql

Do it like this:

Declare @a int;
Declare @b int;

SELECT @a=StartNum,@b=EndNum FROM Users Where UserId = '1223'

PRINT @a
PRINT @b

Solution 3 - Sql

If you are doing this in a stored procedure and don't want the result of the select in an output resultset you will need to use the word INTO.

Declare @a int;
Declare @b int;

SELECT StartNum, EndNum 
FROM Users 
Where UserId = '1223'
INTO @a, @b;

It also can be used like this:

SELECT StartNum, EndNum 
INTO @a, @b
FROM Users 
Where UserId = '1223';

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionOliverView Question on Stackoverflow
Solution 1 - Sqljuergen dView Answer on Stackoverflow
Solution 2 - SqlaF.View Answer on Stackoverflow
Solution 3 - SqlrvazquezglezView Answer on Stackoverflow