Why set variable does not work in a Dynamic SQL statement

Question

Hello,
I am new to Dynamic T-SQL , and I try to learn it.
I tried two different simple statements which would give me the same expected result.
The first one work

use AdventureWorksDW2019;
EXECUTE sp_executesql
N'SELECT lastname
FROM dimcustomer where title = @Title ',
N'@Title varchar(50)',
@Title = 'mr.';

The second one does not. It gave me an error saying that 'Must declare the scalar variable "@Title ".' which I already declared on line 3 below :

declare @Title varchar(50);
declare @alenzi varchar(500);
set @Title = N'mr.';

set @alenzi = N'SELECT name
FROM dimcustomer where title = @Title ';

print (@alenzi )

exec (@alenzi );

I would like know what I did wrong , and how to correct it.

Many Thanks,
Du

Answer 1

declare @title varchar(50);
declare @sql varchar(500);
set @title = N'mr.';

set @sql = N'SELECT lastname
FROM dimcustomer where title = ''' + @title + '''';

print (@sql)

exec (@sql);

Answer 2

Again, I don't think you are at the stage where you should start playing with dynamic SQL. But if you insist, I recommend that you read my article The Curse and Blessings of Dynamic SQL. You will find that it is quite a long article, although you can start with just the first chapter after the introduction.

Answer 3

Thank you so much for all of your help. I am going to read your article.

Answer 4

Hi @minh tran ,

Please also check：

declare @title varchar(50);  
declare @sql varchar(500);  
set @title = N'mr.';  
  
set @sql = N'SELECT name  
FROM dimcustomer where title = @title';  
  
exec sp_executesql @sql,  
  N'@title VARCHAR(50) OUTPUT',  
  @title OUTPUT;  

If your SQL is actually more complicated, then you need to stop using EXEC() and embrace sp_executesql. One of the things this allows is much more flexibility with strongly-typed parameters (including OUTPUT).

' is the boundary character in dynamic sql. If you need to use ' in the middle of the string, you need to use '' to escape the ' in the middle of the string to tell sql server that the ' is not a boundary character.So in the following code, you should add '' on both sides of the variable like Guoxiong did.

    set @sql = N'SELECT name  
    FROM dimcustomer where title = @title';  

When some variables cannot be used as output parameters, you need to splice them.

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.

Answer 5

Thank you so much ALL for all of the great explanation. As a student, I learn very much from you ALL , the experts.