CODEWITHSUNDEEP
t-sqldynamic
MarJer
MarJer

Reputation: 107

How do I get a print of a variable value by executing another variable in sql server?

I want to make print outs of some variables in sql server. The code I'm executing is as below:

declare @r varchar(max), @t char(4)
set @t = 'test'
set @r = '
--Some code that might effecting @t in different ways...

print ''Print value for @t: ''' + @t + '

--Some more code...
'
exec(@r)

When running this I get a msg 102 error, saying Incorrect syntax near 'test'..

I've tried different amounts of apostrphes, sometimes that helps. But it seems to be something else I'm missing here. I just can't get it to work with print outs and now I have quite a few of them and can't use my earlier "work around" in an effective way anymore. So I need to find a solution to this.

Upvotes: 0

Views: 51

Answers (1)

Thom A
Thom A

Reputation: 96028

Don't inject parameters into dynamic SQL. Parametrise them:

DECLARE @r nvarchar(max), @t char(4);
SET @t = 'test';

SET @r = '
--Some code that might effecting @t in different ways...

PRINT ''Print value for @t: '' + @t;

--Some more code...
'
EXEC sys.sp_executesql @R, N'@t char(4)', @t;

Upvotes: 1

Related Questions