sql-server - 在 T-SQL 存储过程中动态检索参数名称和当前值

Question

我有数百个用于商业智能的模板化 ETL 存储过程。他们将运营事件记录到审计表中。缺少的一件事是记录传递给它们的参数信息。问题是从一个 SP 到另一个 SP 的参数并不总是相同的。我正在寻找一段标准代码，我可以将其粘贴到可以循环遍历 proc 的所有参数并检索传入的当前值的过程中。我计划将它们混合在一个字符串中以记录到表中。有什么想法吗？

提前感谢您的指点! -蒂姆

Answer 1

我正在寻找一段标准代码，我可以将其粘贴到可以循环遍历 proc 的所有参数并检索传入的当前值的过程中--

您可以使用以下查询获取为 sp 传递的所有值

例子:
我有下面的存储过程，它为我提供了销售详细信息(仅供演示)

alter  proc dbo.getsales
(
@salesid int
)
as
begin
select 
* from sales where cust_id=@salesid
end

我已经像下面这样调用了我的 sp..

exec  dbo.getsales 4

现在如果我想传递值，我可以使用下面的查询

select top 10* from sys.dm_exec_cached_plans cp
cross apply
sys.dm_exec_text_query_plan(cp.plan_handle,default,default)
where objtype='proc'

下面显示的是编译时值

话虽如此，有很多事情要考虑..我们可以使用xml方法来获取这个值

现在会发生什么，如果我再次为 2 的值运行相同的存储过程..

<ColumnReference Column="@salesid" ParameterCompiledValue="(4)" ParameterRuntimeValue="(2)" />

这里的一个重要问题是，当我选择要从 ssms 显示的执行计划时，会显示上述值。

但是缓存中的值是多少，让我们再次使用上面的计划缓存查询看看它

<ColumnReference Column="@salesid" ParameterCompiledValue="(4)"/>

它仍然显示编译值，加上 usecounts 列为 5--`这意味着该计划已被使用 5 次，最初编译计划时传递的参数为 4。这也意味着，运行时值不是存储在缓存的计划详细信息中..

所以总而言之，您可以获得传递给存储过程的运行时值

• 1.语句编译时传递的值(
  您可以在一段时间内开始收集此信息并将它们记录到存储过程中，我认为随着时间的推移服务器重新启动，计划重新编译您可以获得一组新的参数值)
• 2.与开发团队取得联系也是一个好方法，因为他们可以为您提供可以传递的参数的完整列表，如果这 运动量大