How to assign a column value to a variable name?

minh tran 76

Hello,
I am new to Transaction query, and I want to learn it.
I am trying to assign a last column value on a table to a variable and it worked if I try my simple sql statement below :

declare @location nvarchar(50);

select @location = location FROM dbo.productcategory order by ProductCategoryID desc;
print @location

However, it does not work in the new sql statement below and it gave me an error such as "Must declare the scalar variable "@alenzi ". which I already declare below. Does anybody know what I did wrong?

declare @location nvarchar(50);
declare @alenzi nvarchar(50);

set @alenzi = N'select @location' + ' = location FROM dbo.productcategory order by ProductCategoryID desc'

exec sys.sp_executesql @alenzi ;

print @location

Thank you,
Du

Erland Sommarskog 100.8K Reputation points MVP

2021-11-09T22:36:52.65+00:00
I am trying to assign a last column value on a table to a variable and it worked if I try my simple sql statement below :

>

declare @location nvarchar(50);

>

select @location = location FROM dbo.productcategory order by ProductCategoryID desc;
print @location

Viorel has showed you how to use dynamic SQL with output parameters. However, if you are new to SQL, you should not deal with dynamic SQL - that is an advanced feature.

I would also like to point out an error in the above. There is no such thing as "last value" in a table - a table is by definition an unordered object.

And even if you mean "the row with the highest ProductCategoryID", I would expect this to give you the location for the lowest ProductCategoryID. I think the documentation says that the variable is assign the "last value returned" - whatever that means.

You can use TOP (1) to make the query unambiguous:

declare @location nvarchar(50); select TOP(1) @location = location FROM dbo.productcategory order by ProductCategoryID desc; print @location
EchoLiu-MSFT 14,571 Reputation points

2021-11-12T01:21:46.247+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!
minh tran 76 Reputation points

2021-11-12T17:26:40.737+00:00

Thank you much for all of the helps. It works well. I have tried different ways and they all given same results.

Way 1: embedded parameter
use AdventureWorksDW2019

EXEC sp_executesql N'SELECT [birthdate]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [dimcustomer]
WHERE [title] = ''Mr.'''

--- Way 2 embedded parameter

declare @alenzi nvarchar (max) = N'SELECT birthdate
,Title
,FirstName
,[MiddleName]
,[LastName]
FROM [dimcustomer]
WHERE [title] = ''Mr.'''

exec sp_executesql @alenzi ;

-- Way 3 passing parameters

use AdventureWorksDW2019
DECLARE @params NVARCHAR(4000) = '@Title NVARCHAR(max)';

declare @alenzi nvarchar (max) = N'SELECT birthdate
,Title
,FirstName
,[MiddleName]
,[LastName]
FROM [dimcustomer]
WHERE [title] = @Title '

exec sp_executesql @alenzi , @params, @Title = 'Mr.';
EchoLiu-MSFT 14,571 Reputation points

2021-11-15T08:04:53.403+00:00

Thank you for your update. But please select the answer you find most useful and mark it as an answer so that we can close this CASE.

Regards
Echo

9 answers

Viorel 111.8K Reputation points

2021-11-09T22:02:10.67+00:00
Try these statements:

set @sql = N'select top(1) @location = location FROM dbo.productcategory order by ProductCategoryID desc' exec sys.sp_executesql @sql, @params = N'@location nvarchar(50) OUTPUT', @location = @location OUTPUT
Please sign in to rate this answer.
minh tran 76 Reputation points

2021-11-09T23:36:08.027+00:00

Thank you Viorel. I tried the statement and I got an error on Invalid object name 'dbo.produc'. I wonder why It did not read the whole table name dbo.productcategory. I am still trying to figure it out.

Viorel 111.8K Reputation points

2021-11-10T06:17:04.217+00:00

Increase the size of your variables, for example: 'declare @alenzi nvarchar(100)' or 'declare @alenzi nvarchar(max)'. Show your current code that does not work.
Sign in to comment
EchoLiu-MSFT 14,571 Reputation points

2021-11-10T03:16:30.983+00:00
Hi @minh tran ,

Welcome to the microsoft TSQL Q&A forum!

The three posts you posted are the same, so I merged them for you.

Please refer to:

declare @location nvarchar(max); declare @sql nvarchar(max); set @sql = N'select top(1) @location = location FROM dbo.productcategory order by ProductCategoryID desc' exec sys.sp_executesql @sql, N'@location VARCHAR(MAX) OUTPUT', @location OUTPUT; print @location;

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
minh tran 76 Reputation points

2021-11-10T15:19:44.023+00:00

Thank you so much. It worked!!
Sign in to comment
minh tran 76 Reputation points

2021-11-10T15:20:29.957+00:00

Thank you so much All for all of the helps. It worked very well!!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
minh tran 76 Reputation points

2021-11-10T15:26:23.19+00:00

I have one question regarding the variable @location from the solution above.

I try to figure it out why does the variable @location need to appear twice on line 1 as

declare @location nvarchar(max);

and again on line 7 as

N'@location VARCHAR(MAX) OUTPUT',

Could I just drop the VARCHAR(MAX) and just have this on line 7 as

N'@location OUTPUT',

Would it work ? and I try to figure out why it did not.

Many thanks for explanation why it would not work!
Please sign in to rate this answer.
Tom Phillips 17,716 Reputation points

2021-11-10T15:49:48.373+00:00

See:
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15#syntax

Erland Sommarskog 100.8K Reputation points MVP

2021-11-10T21:52:52.98+00:00

I've already said that dynamic SQL is not a feature that is apt from the beginning. Have you gotten started with stored procedures? You should learn these first. Then this makes a little more sense.

sp_executesql creates a nameless stored procedure and saves it in memory only and executes it at the same time. The syntax for the second parameter is exactly the syntax for a parameter list a stored procedure.

That is, you are created a nested object, and that's why you need to repeat the data type.

Also, @location should not be nvarchar(MAX). The SQL string should be nvarchar(MAX) because when you build an SQL string you don't know how long it gets. But something that is normal data, should have normal length,
Sign in to comment
minh tran 76 Reputation points

2021-11-10T16:01:26.7+00:00

Thank you so much. I see that the syntax requires that I need to have @Kritivasas name, its data type, and input or output parameter. So it did not work if I simply had @Kritivasas name , and input or output parameter because the data type was missing.
Please sign in to rate this answer.
EchoLiu-MSFT 14,571 Reputation points

2021-11-11T02:12:12.703+00:00

I think you can simply understand that this is a grammatical convention for dynamic sql. In fact, there are many complicated grammars in dynamic sql, which will be gradually understood as you learn dynamic sql. If you have any questions about these grammars, you can refer to the official Microsoft documents, which have detailed introductions and examples for almost all TSQL grammars for your practice.

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo
Sign in to comment