I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement.
EG:
Declare @SQL nvarchar(max), @Params nvarchar(max), @DebugMode bit, @Foobar int
select @DebugMode=1,@Foobar=364556423
set @SQL='Select @Foobar'
set @Params=N'@Foobar int'
if @DebugMode=1 print @SQL
exec sp_executeSQL @SQL,@Params
,@Foobar=@Foobar
The print results of the above code are simply "Select @Foobar". Is there any way to dynamically print the values & variable names of the sql being executed? Or when doing the print, replace parameters with their actual values so the SQL is re-runnable?
I have played with creating a function or two to accomplish something similar, but with data type conversions, pattern matching truncation issues, and non-dynamic solutions. I'm curious how other developers solve this issue without manually printing each and every variable manually.
Best Answer
One way to get this done is probably something you have already done, and that is to replace your line:
with
And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.
You could also use RAISERROR in a similar fashion:
HTH