SQL Server Profiler 中的存储过程输出参数
我有一个带有 int 输出参数的存储过程。如果我运行 SQL Server Profiler，通过一些 .Net 代码执行存储过程，并捕获 RPC:Completed 事件，则 TextData 如下所示：
declare @p1 int
set @p1=13
exec spStoredProcedure @OutParam=@p1 output
select @p1
为什么它看起来像是在执行存储过程之前获取输出参数的值？
I've got a stored procedure with an int output parameter. If I run SQL Server Profiler, execute the stored procedure via some .Net code, and capture the RPC:Completed event, the TextData looks like this:
declare @p1 int
set @p1=13
exec spStoredProcedure @OutParam=@p1 output
select @p1
Why does it look like it's getting the value of the output parameter before executing the stored procedure?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
RPC:Completed 事件类指示远程过程调用已完成。所以此时输出参数实际上是已知的。看看跟踪 RPC:Started 是否显示出您所期望的结果。
The RPC:Completed event class indicates that a remote procedure call has been completed. So the output parameter is actually known at that point. See if tracing the RPC:Started shows you what you expect.
无论你怎么看，这都是一个错误。 SQL Profiler“TextData”的目的是使某人能够理解并重复存储过程调用。在这种情况下，如果
spStoredProcedure过程具有任何依赖于@OutParam 的输入值的逻辑，则运行此 T-SQL 可能会给出完全不同的结果参数，其中“13”的值作为输入值是有意义的。很容易看出它有多方便（使您能够查看 proc 调用的输出值，否则需要与“RPC 输出参数”事件相关），但对于 T 的内容来说，这实际上是一个“谎言” -SQL 等效项已执行。
相关：我刚刚看到 Microsoft 客户服务和支持团队的一篇文章 - 关于另一种情况，将 RPC:Completed 事件的 BinaryData 转换为可显示的 TextData 值会导致原始 RPC 调用的再现不准确 - 这次出现代码页问题：
http://blogs.msdn.com/b/psssql/archive/2008/01/24/how-it-works-conversion-of-a -varchar-rpc-parameter-to-text-from-a-trace-trc-capture.aspx
更新：通过对此进行实验，我发现了行为的另一个特点 - 探查器只会使用这个不正确的初始设置如果 RPC 调用中该参数的输入值为
Null。如果提供了非空值（并且 .Net SqlClient 中的参数具有方向“InputOutput”），则初始 SET 保存真实的输入值，而不是结果输出值。但如果输入为空，则设置输出值。此观察结果支持这样的观点：这只是探查器 RPC 到 TSQL 显示转换中的空处理错误。
This is, no matter how you look at it, a bug. The intent of the SQL Profiler "TextData" is to enable someone to understand and repeat the stored procedure call. In this case, running this T-SQL can give you a completely different result, if the
spStoredProcedureprocedure has any logic dependent on the input value of the@OutParamparameter, where that value of "13" were somehow meaningful as an input value.It's easy to see how it can be convenient (enables you to see the output values of the proc call, which would otherwise need to do with the "RPC Output Parameter" event), but it is effectively a "lie" as to what T-SQL equivalent was executed.
RELATED: I just came across an article from the Microsoft Customer Service and Support team - about another case where conversion of the RPC:Completed event's BinaryData into a displayable TextData value results in an inaccurate reproduction of the original RPC call - this time codepage issues:
http://blogs.msdn.com/b/psssql/archive/2008/01/24/how-it-works-conversion-of-a-varchar-rpc-parameter-to-text-from-a-trace-trc-capture.aspx
UPDATED: By experimenting with this, I found another peculiarity of the behaviour - the profiler will only use this incorrect initial SET if the input value for that parameter, in the RPC call, was
Null. If a non-null value was provided (and the parameter, in .Net SqlClient, had direction "InputOutput"), then that initial SET holds the true input value, and not the resulting output value. But if the input was null, then the output value is set instead.This observation supports the notion that this is simply a null-handling bug in the profiler RPC-to-TSQL display conversion.