Setting two scalar variables in one SELECT statement?
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?
DECLARE @a int;
DECLARE @b int;
SELECT @a = StartNum, @b = EndNum
FROM Users
WHERE UserId = '1223'
Do it like this:
Declare @a int;
Declare @b int;
SELECT @a=StartNum,@b=EndNum FROM Users Where UserId = '1223'
PRINT @a
PRINT @b
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';