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 101.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

minh tran 76 Reputation points

2021-11-12T15:09:56.163+00:00

Thank you so much ALL for all of the help. I tried different ways of executesql and it works perfectly. Those include embedded a value into the string and I tried to pass a parameter value into the string. Both ways works.

Please suggest what I should do next regarding my learning of dynamic sql. I have some experienced with python, java and I use regex quite a bit. Could I embed a regeex expression into a query string and makes it search for all possible values ?

Thanks for all of the helps.
Du
Please sign in to rate this answer.
Erland Sommarskog 101.8K Reputation points MVP

2021-11-12T22:43:16.313+00:00

Please suggest what I should do next regarding my learning of dynamic sql.

I repeat what I have said before: First learn to write static SQL queries thoroughly. But if you really want to go on, read my article. But you may find that by time, things are getting over your head. That is the hint, that you need to learn SQL in general first.
Sign in to comment
minh tran 76 Reputation points

2021-11-13T12:03:18.347+00:00

Thank you so much for all of the help. I am going to follow your advice.
Regards,
Du
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Erland Sommarskog 101.8K Reputation points MVP

2021-11-13T12:28:54.553+00:00

In case you find my article too much to swallow at a time, or you prefer a different format, I strongly recommend watching Kenneth Fisher's presentation Demystifying Dynamic SQL from PASS last year: https://www.youtube.com/watch?v=CMr_PLiXzUw&list=PLoGAcXKPcRva915OeutOXOtFKN19WSL4I&index=170.
Please sign in to rate this answer.

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

2021-11-13T13:59:13.663+00:00

Thank you so much for recommendations. I am going to watch the supplementary tutorial as well.
Regards,
Du
Please sign in to rate this answer.

0 comments No comments
Sign in to comment