Xtykutl

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!

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

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;

If just assigning a single variable, it is usually best to assign with SET and ensure only 1 result is returned from your query.

 SET @localVariable = (SELECT TOP 1 @localVariable = userId FROM [user] ORDER BY userId)

 -- Or using WHERE to ensure only 1 record returned

While you can use SELECT for variable assignment, it can hide some underlying issues (when a query returns either no results and doesn't change the value at all, or if query returns more than 1 result and you don't know which row actually populated the value).

Here is the official MSDN docs on this practice:

SELECT @local_variable is typically used to return a single value into
the variable. However, when expression is the name of a column, it can
return multiple values. If the SELECT statement returns more than one
value, the variable is assigned the last value that is returned.

If the SELECT statement returns no rows, the variable retains its
present value. If expression is a scalar subquery that returns no
value, the variable is set to NULL.

One SELECT statement can initialize multiple local variables.

See MSDN: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-2017

However, if used properly, SELECT can be used and be slightly faster given if assignment multiple variables from same query, you only have to run the query once.

SELECT TOP 1 @localVariable = userId FROM user ORDER BY userId

-- Or using WHERE to ensure only 1 record returned

Please not that you cannot both assign a variable and return columns from a result set in the same query.