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);
Why set variable does not work in a Dynamic SQL statement
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
7 answers
Sort by: Most helpful
-
Guoxiong 8,206 Reputation points
2021-11-10T21:30:58.567+00:00 -
Erland Sommarskog 116.5K Reputation points MVP
2021-11-10T22:27:21.007+00:00 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.
-
minh tran 216 Reputation points
2021-11-10T22:35:06.11+00:00 Thank you so much for all of your help. I am going to read your article.
-
EchoLiu-MSFT 14,601 Reputation points
2021-11-11T02:43:47.717+00:00 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. -
minh tran 216 Reputation points
2021-11-11T14:39:17.733+00:00 Thank you so much ALL for all of the great explanation. As a student, I learn very much from you ALL , the experts.