打印动态参数值

Question

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

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

上述代码的打印结果只是“Select @Foobar”。有什么方法可以动态打印值&正在执行的sql的变量名？或者在进行打印时，将参数替换为实际值，以便 SQL 可以重新运行？

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

Answer 1

我不相信评估的语句可用，这意味着您的示例查询“Select @FooBar”永远不会在任何地方保留为“Select 364556243”

即使在探查器跟踪中，您也会看到该语句以“(@Foobar int)select @foobar”的形式命中缓存这

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

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

所有这些都可以清理并包装在一个很好的函数中，其中包含输入（@Statement、@ParamDef、@ParamVal）和将返回“准备好的”声明。我会留下一些作为练习给您，但请在您改进后回复！

使用这里的 split 函数 链接

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），但我只会尝试 1 个奇怪的字符串值，例如“agd”。
• 针对我正在做的事情，针对具有代表性大小和数据值分布的数据集运行这些值。使用您最喜欢的数据生成工具（市场上有几个不错的工具）来加速这一过程。
• 我通常会在更临时的基础上进行这样的调试，因此从 SSMS 结果窗口收集结果就足够了。

我能想到的最好方法是使用 SQL 跟踪捕获通过网络传输的查询。如果您在查询字符串中放置一些唯一的内容（作为注释），则可以很容易地在跟踪中为其应用过滤器，这样您就不会捕获超出您需要的内容。

然而，这并不全是桃子和桃子。奶油。

这仅适用于开发环境，也许 QA，具体取决于您的商店的严格程度。

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

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

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

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