Print Dynamic Parameter Values

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.

Answer 1

I dont believe the evaluated statement is available, meaning your example query 'Select @FooBar' is never persisted anywhere as 'Select 364556243'

Even in a profiler trace you would see the statement hit the cache as '(@Foobar int)select @foobar'

This makes sense, since a big benefit of using sp_executesql is that it is able to cache the statement in a reliable form without variables evaluated, otherwise if it replaced the variables and executed that statement we would just see the execution plan bloat.

updated: Here's a step in right direction:

All of this could be cleaned up and wrapped in a nice function, with inputs (@Statement, @ParamDef, @ParamVal) and would return the "prepared" statement. I'll leave some of that as an exercise for you, but please post back when you improve it!

Uses split function from here link

set nocount on;

declare @Statement  varchar(100),   -- the raw sql statement
        @ParamDef   varchar(100),   -- the raw param definition
        @ParamVal   xml             -- the ParamName -to- ParamValue mapping as xml


-- the internal params:
declare @YakId int,
        @Date datetime
select  @YakId = 99,
        @Date = getdate();


select  @Statement = 'Select * from dbo.Yak where YakId = @YakId and CreatedOn > @Date;',
        @ParamDef = '@YakId int, @Date datetime';

-- you need to construct this xml manually... maybe use a table var to clean this up
set @ParamVal = (   select *
                    from    (   select '@YakId', cast(@YakId as varchar(max)) union all
                                select '@Date', cast(@Date as varchar(max))
                            ) d (Name, Val)
                    for xml path('Parameter'), root('root')
                )

-- do the work
declare @pStage table (pName varchar(100), pType varchar(25), pVal varchar(100));
;with 
    c_p (p)
    as  (   select  replace(ltrim(rtrim(s)), ' ', '.')
            from    dbo.Split(',', @ParamDef)d
        ),
    c_s (pName, pType)
    as  (   select  parsename(p, 2), parsename(p, 1)
            from    c_p
        ),
    c_v (pName, pVal)
    as  (   select  p.n.value('Name[1]', 'varchar(100)'),
                    p.n.value('Val[1]', 'varchar(100)')
            from    @ParamVal.nodes('root/Parameter')p(n)
        )
insert into @pStage
    select  s.pName, s.pType, case when s.pType = 'datetime' then quotename(v.pVal, '''') else v.pVal end -- expand this case to deal with other types
    from    c_s s
    join    c_v v on
            s.pName = v.pName

-- replace pName with pValue in statement
select  @Statement = replace(@Statement, pName, isnull(pVal, 'null'))                       
from    @pStage
where   charindex(pName, @Statement) > 0;

print @Statement;

Answer 2

On the topic of how most people do it, I will only speak to what I do:

• Create a test script that will run the procedure using a wide range of valid and invalid input. If the parameter is an integer, I will send it '4' (instead of 4), but I'll only try 1 oddball string value like 'agd'.
• Run the values against a data set of representative size and data value distribution for what I'm doing. Use your favorite data generation tool (there are several good ones on the market) to speed this up.
• I'm generally debugging like this on a more ad hoc basis, so collecting the results from the SSMS results window is as far as I need to take it.

The best way I can think of is to capture the query as it comes across the wire using a SQL Trace. If you place something unique in your query string (as a comment), it is very easy to apply a filter for it in the trace so that you don't capture more than you need.

However, it isn't all peaches & cream.

This is only suitable for a Dev environment, maybe QA, depending on how rigid your shop is.

If the query takes a long time to run, you can mitigate that by adding "TOP 1", "WHERE 1=2", or a similar limiting clause to the query string if @DebugMode = 1. Otherwise, you could end up waiting a while for it to finish each time.

For long queries where you can't add something the query string only for debug mode, you could capture the command text in a StmtStarted event, then cancel the query as soon as you have the command.

If the query is an INSERT/UPDATE/DELETE, you will need to force a rollback if @DebugMode = 1 and you don't want the change to occur. In the event you're not currently using an explicit transaction, doing that would be extra overhead.

Should you go this route, there is some automation you can achieve to make life easier. You can create a template for the trace creation and start/stop actions. You can log the results to a file or table and process the command text from there programatically.