How to assign a column value to a variable name?

minh tran 46 Reputation points
2021-11-09T21:50:29.217+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,560 questions
{count} votes

9 answers

Sort by: Most helpful
  1. Viorel 82,906 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
    

  2. EchoLiu-MSFT 14,416 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.


  3. minh tran 46 Reputation points
    2021-11-10T15:20:29.957+00:00

    Thank you so much All for all of the helps. It worked very well!!

    No comments

  4. minh tran 46 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!


  5. minh tran 46 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.