how to assign query result to local variable in T-SQL
up vote
0
down vote
favorite
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!
sql sql-server tsql variables
add a comment |
up vote
0
down vote
favorite
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!
sql sql-server tsql variables
There might be more than one row onuserId
column inuser
table, which row value did you want to store?
– D-Shih
Nov 8 at 13:16
Declare your variable as table and insert your results into it...
– Peter Schneider
Nov 8 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).
– Larnu
Nov 8 at 13:19
1
What exactly do you want to capture? Entire row? Entire table? Nth column from result?
– Salman A
Nov 8 at 13:23
Possible duplicate of SELECT INTO a table variable in T-SQL
– tweray
Nov 8 at 13:23
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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!
sql sql-server tsql variables
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!
sql sql-server tsql variables
sql sql-server tsql variables
edited Nov 10 at 15:05
Salman A
171k65328413
171k65328413
asked Nov 8 at 13:15
toxic
2,274952107
2,274952107
There might be more than one row onuserId
column inuser
table, which row value did you want to store?
– D-Shih
Nov 8 at 13:16
Declare your variable as table and insert your results into it...
– Peter Schneider
Nov 8 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).
– Larnu
Nov 8 at 13:19
1
What exactly do you want to capture? Entire row? Entire table? Nth column from result?
– Salman A
Nov 8 at 13:23
Possible duplicate of SELECT INTO a table variable in T-SQL
– tweray
Nov 8 at 13:23
add a comment |
There might be more than one row onuserId
column inuser
table, which row value did you want to store?
– D-Shih
Nov 8 at 13:16
Declare your variable as table and insert your results into it...
– Peter Schneider
Nov 8 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).
– Larnu
Nov 8 at 13:19
1
What exactly do you want to capture? Entire row? Entire table? Nth column from result?
– Salman A
Nov 8 at 13:23
Possible duplicate of SELECT INTO a table variable in T-SQL
– tweray
Nov 8 at 13:23
There might be more than one row on
userId
column in user
table, which row value did you want to store?– D-Shih
Nov 8 at 13:16
There might be more than one row on
userId
column in user
table, which row value did you want to store?– D-Shih
Nov 8 at 13:16
Declare your variable as table and insert your results into it...
– Peter Schneider
Nov 8 at 13:18
Declare your variable as table and insert your results into it...
– Peter Schneider
Nov 8 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).– Larnu
Nov 8 at 13:19
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).– Larnu
Nov 8 at 13:19
1
1
What exactly do you want to capture? Entire row? Entire table? Nth column from result?
– Salman A
Nov 8 at 13:23
What exactly do you want to capture? Entire row? Entire table? Nth column from result?
– Salman A
Nov 8 at 13:23
Possible duplicate of SELECT INTO a table variable in T-SQL
– tweray
Nov 8 at 13:23
Possible duplicate of SELECT INTO a table variable in T-SQL
– tweray
Nov 8 at 13:23
add a comment |
3 Answers
3
active
oldest
votes
up vote
3
down vote
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 need to quote the object name. This won't compile as is.
– Larnu
Nov 8 at 13:22
@Larnu good catch!
– Zohar Peled
Nov 8 at 13:23
add a comment |
up vote
0
down vote
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.
Yes, I had just copied original sample query. Added[user]
for clarity.
– Jason W
Nov 8 at 14:30
add a comment |
up vote
0
down vote
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;
USER
is a reserved word.
– Larnu
Nov 8 at 13:33
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
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 need to quote the object name. This won't compile as is.
– Larnu
Nov 8 at 13:22
@Larnu good catch!
– Zohar Peled
Nov 8 at 13:23
add a comment |
up vote
3
down vote
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 need to quote the object name. This won't compile as is.
– Larnu
Nov 8 at 13:22
@Larnu good catch!
– Zohar Peled
Nov 8 at 13:23
add a comment |
up vote
3
down vote
up vote
3
down vote
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 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];
edited Nov 8 at 13:22
answered Nov 8 at 13:19
Zohar Peled
50.8k73171
50.8k73171
You need to quote the object name. This won't compile as is.
– Larnu
Nov 8 at 13:22
@Larnu good catch!
– Zohar Peled
Nov 8 at 13:23
add a comment |
You need to quote the object name. This won't compile as is.
– Larnu
Nov 8 at 13:22
@Larnu good catch!
– Zohar Peled
Nov 8 at 13:23
You need to quote the object name. This won't compile as is.
– Larnu
Nov 8 at 13:22
You need to quote the object name. This won't compile as is.
– Larnu
Nov 8 at 13:22
@Larnu good catch!
– Zohar Peled
Nov 8 at 13:23
@Larnu good catch!
– Zohar Peled
Nov 8 at 13:23
add a comment |
up vote
0
down vote
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.
Yes, I had just copied original sample query. Added[user]
for clarity.
– Jason W
Nov 8 at 14:30
add a comment |
up vote
0
down vote
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.
Yes, I had just copied original sample query. Added[user]
for clarity.
– Jason W
Nov 8 at 14:30
add a comment |
up vote
0
down vote
up vote
0
down vote
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.
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.
edited Nov 8 at 14:29
answered Nov 8 at 13:29
Jason W
10.1k21342
10.1k21342
Yes, I had just copied original sample query. Added[user]
for clarity.
– Jason W
Nov 8 at 14:30
add a comment |
Yes, I had just copied original sample query. Added[user]
for clarity.
– Jason W
Nov 8 at 14:30
Yes, I had just copied original sample query. Added
[user]
for clarity.– Jason W
Nov 8 at 14:30
Yes, I had just copied original sample query. Added
[user]
for clarity.– Jason W
Nov 8 at 14:30
add a comment |
up vote
0
down vote
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;
USER
is a reserved word.
– Larnu
Nov 8 at 13:33
add a comment |
up vote
0
down vote
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;
USER
is a reserved word.
– Larnu
Nov 8 at 13:33
add a comment |
up vote
0
down vote
up vote
0
down vote
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;
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;
edited Nov 10 at 15:07
answered Nov 8 at 13:25
Salman A
171k65328413
171k65328413
USER
is a reserved word.
– Larnu
Nov 8 at 13:33
add a comment |
USER
is a reserved word.
– Larnu
Nov 8 at 13:33
USER
is a reserved word.– Larnu
Nov 8 at 13:33
USER
is a reserved word.– Larnu
Nov 8 at 13:33
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53208523%2fhow-to-assign-query-result-to-local-variable-in-t-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
There might be more than one row on
userId
column inuser
table, which row value did you want to store?– D-Shih
Nov 8 at 13:16
Declare your variable as table and insert your results into it...
– Peter Schneider
Nov 8 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).– Larnu
Nov 8 at 13:19
1
What exactly do you want to capture? Entire row? Entire table? Nth column from result?
– Salman A
Nov 8 at 13:23
Possible duplicate of SELECT INTO a table variable in T-SQL
– tweray
Nov 8 at 13:23