CODEWITHSUNDEEP
sqlsql-servert-sqlvariables
K.Z
K.Z

Reputation: 5075

how to assign query result to local variable in T-SQL

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!

Upvotes: 1

Views: 3099

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272216

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;

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82504

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];

Upvotes: 4

Related Questions