Print Parameters in Dynamic SQL

Question

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.

SQLRockstar · Accepted Answer · 2011-03-28 22:51:21Z

5

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

with

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)

HTH

answered Mar 28, 2011 at 22:51

SQLRockstar

6,36527 silver badges48 bronze badges

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
– Brent D

2011-03-29 16:33:04 +00:00
Commented Mar 29, 2011 at 16:33

Add a comment |

Stack Exchange Network

Print Parameters in Dynamic SQL

1 Answer 1

Your Answer

Hot Network Questions

Print Parameters in Dynamic SQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions