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.


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

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.


One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL


if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

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:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)


  • Unfortunately this would mean manually writing replaces for each and every variable used in the dynamic statement. This can get unwieldy when 20+ variables are used and changed or when multiple dynamic sql statements are being built concurrently. – Brent D Mar 29 '11 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.