打印动态参数值

Question

我将动态SQL用于许多任务，并且不断遇到相同的问题:打印动态T-SQL语句中使用的变量的值。

例如：

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

上面代码的打印结果就是"Select @Foobar“。有没有办法动态地打印正在执行的sql的值和变量名？或者在执行打印时，将参数替换为它们的实际值，以便SQL可重新运行？

我尝试过创建一两个函数来实现类似的功能，但是使用了数据类型转换、模式匹配截断问题和非动态解决方案。我很好奇其他开发人员如何在不手动打印每个变量的情况下解决这个问题。

Answer 1

我不相信求值语句是可用的，这意味着您的示例查询'Select @FooBar‘永远不会持久化为'Select 364556243’。

即使在分析器跟踪中，您也会看到语句命中缓存为'(@Foobar int)select @foobar‘

这是有道理的，因为使用sp_executesql的一个很大的好处是它能够以一种可靠的形式缓存语句，而不需要计算变量，否则如果它替换变量并执行该语句，我们只会看到执行计划膨胀。

更新：这是朝着正确方向迈出的一步

所有这些都可以通过输入(@ statement，@ParamDef，@ParamVal)清理并包装在一个很好的函数中，并返回“准备好的”语句。我会把其中的一些作为练习留给你，但是当你改进它的时候请回帖！

使用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

关于大多数人是如何做到这一点的，我只会谈谈我做的事情：

• 创建一个测试脚本，该脚本将使用各种有效和无效的输入来运行过程。如果参数是一个整数，我会发送给它'4‘(而不是4)，但我只会尝试一个奇怪的字符串值，比如’agd‘。
• 根据我正在做的事情的代表性大小和数据值分布的数据集来运行这些值。使用您最喜欢的数据生成工具(市面上有几个不错的工具)来加速此过程。
• 我通常会在更临时的基础上像这样进行调试，因此从SSMS结果窗口中收集结果就是我所需要的了。

我能想到的最好的方法是使用SQL跟踪在查询到达时捕获它。如果您在查询字符串中放置了一些独特的内容(作为注释)，那么很容易在跟踪中为它应用一个过滤器，这样您就不会捕获超过所需的内容。

然而，这并不是所有的桃子和奶油。

这只适用于开发环境，可能是QA，这取决于你的商店有多严格。

如果查询运行时间较长，您可以通过在查询字符串中添加"TOP 1“、"WHERE 1=2”或类似的限制子句(如果@DebugMode = 1 )来缓解这种情况。否则，您可能每次都要等待一段时间才能完成。

对于不能仅在调试模式下添加查询字符串的长查询，可以在StmtStarted事件中捕获命令文本，然后在获得命令后立即取消查询。

如果查询是INSERT/UPDATE/DELETE，当@DebugMode =1并且您不希望发生更改时，您将需要强制回滚。如果您当前没有使用显式事务，那么这样做会带来额外的开销。

如果你走这条路，你可以实现一些自动化，让生活变得更容易。您可以为跟踪创建和启动/停止操作创建模板。您可以将结果记录到文件或表中，并从那里以编程方式处理命令文本。