Print dynamic parameter values

Question

Print dynamic parameter values

I used dynamic SQL for many tasks and constantly faced the same problem: printing the 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 just "Select @Foobar". Is there a way to dynamically print the values and variable names of an executable sql? Or, when printing, replace the parameters with their actual values so that SQL is restarted?

I played with creating a function or two to achieve something similar, but with data type conversions, template truncation problems, and non-dynamic solutions. I am curious how other developers solve this problem without manually printing each variable.

+3

debugging sql sql-server sql-server-2008 sql-server-2005

Brent d 05 '11 20:11

2

, , , :

script, , . , "4" ( 4), oddball, "agd".
, . ( ), .
, SSMS .

, , , SQL Trace. - ( ), , , .

.

Dev, , QA, , .

, , "TOP 1", "WHERE 1 = 2" , @DebugMode = 1. , , .

, , StmtStarted, , .

INSERT/UPDATE/DELETE, , @DebugMode = 1, , . , , .

, , . /. .

+2

Phil Helmer 06 '11 1:02

Nathan Skerl · Accepted Answer · 2011-05-06T16:26:39+0000

, , , " @FooBar" , " 364556243"

, '(@Foobar int), @foobar'

, sp_executesql , , , , .

: :

, (@Statement, @ParamDef, @ParamVal) "" . , , , , !

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;

Print dynamic parameter values

More articles: