1

I need to stored/ assign query result of multiple columns to local variable

for example;

declare @localVariable

select @localVariable = userId, userName, email, address 
from user;

I believe the above code is not correct!

Salman A
  • 262,204
  • 82
  • 430
  • 521
K.Z
  • 5,201
  • 25
  • 104
  • 240
  • There might be more than one row on `userId` column in `user` table, which row value did you want to store? – D-Shih Nov 08 '18 at 13:16
  • Declare your [variable as table](https://odetocode.com/articles/365.aspx) and insert your results into it... – Peter Schneider Nov 08 '18 at 13:18
  • `FROM user` won't work, `USER` is a reserved word in T-SQL. You would need to quote your object name (or better yet, rename your object to a name that isn't a reserved word). – Thom A Nov 08 '18 at 13:19
  • 1
    What exactly do you want to capture? Entire row? Entire table? Nth column from result? – Salman A Nov 08 '18 at 13:23
  • Possible duplicate of [SELECT INTO a table variable in T-SQL](https://stackoverflow.com/questions/3838240/select-into-a-table-variable-in-t-sql) – tweray Nov 08 '18 at 13:23

2 Answers2

4

You need to declare a table variable for that:

DECLARE @localVariable AS TABLE
(
    userId int,
    userName varchar(10),
    email nvarchar(100),
    address nvarchar(1000)
);

INSERT INTO @localVariable (userId, userName, email, address)
SELECT userId, userName, email, address
FROM [user];
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

You cannot mix variable assignment and data-retrieval inside a select statement. This will not work:

DECLARE @localVariable INT;
SELECT TOP 1 @localVariable = userId, userName FROM user;

But this will:

-- copy values from multiple columns to variables
DECLARE @localVariable1 INT;
DECLARE @localVariable2 VARCHAR(100);
SELECT TOP 1 @localVariable1 = userId, @localVariable2 = userName FROM user;
Salman A
  • 262,204
  • 82
  • 430
  • 521