variables in sp_executesql

Question

I have a piece of code:

DECLARE @v int;  
DECLARE @SQLString nvarchar(500);  
DECLARE @ParmDefinition nvarchar(500);  
DECLARE @max_title varchar(30);  

SET @IntVariable = 197;  
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)   
   FROM some_table 
   WHERE name = @level';  
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';  

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @v, @max_titleOUT=@max_title OUTPUT;  
SELECT @max_title;

I'm a little bit confused about this line:

       WHERE name = @level';

If @v is a string instead of an int variable, should I put quotes around @level like this?

       WHERE name = ''@level''';

When should I put quotes around a variable inside the @SQLString?

score 2 · Answer 1 · answered May 27 '17 at 07:43

2

No, @level and @v are variables. You shouldn't put quotes around.

When should I put quotes around a variable inside the @SQLString?

Nowhere

answered May 27 '17 at 07:43

Backs

24,430
5
58
85

Even `name` is of nvarchar(...) type? Generally we will write statements like `WHERE name = '...'`, right? – Just a learner May 27 '17 at 07:46
@OgrishMan don't miss, that `@level` is a variable. When you write `WHERE name = @level` not in `sp_executesql ` you don't put quotes, right? So, no need to put them in `sp_executesql ` – Backs May 27 '17 at 07:47
Oh, yes! Thank you for the explanation. I'm clear now but I don't know why I feel confused about this just now. Thanks again! – Just a learner May 27 '17 at 07:49
@OgrishMan glad to help, maybe you just need time to get used to it – Backs May 27 '17 at 07:52

variables in sp_executesql

1 Answers1