如何将跟踪/调试输出添加到 Sql Server (2008) 中的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4378804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add tracing/debug output to stored procedures in Sql Server (2008)
提问by Tim Abell
What is the closest to being able to add log4netstyle debug information to a set of stored procedures? Some of procedures delegate work to other procedures and I want trace information from both.
最接近能够将log4net样式调试信息添加到一组存储过程的方法是什么？一些过程将工作委托给其他过程，我想要来自两者的跟踪信息。
I've sprinkled print and select statements through while developing them, and ideally would like to be able to run the proc in different modes depending on whether it's normal operation of troubleshooting and get more or less output.
我在开发它们时已经撒了打印和选择语句，理想情况下希望能够在不同的模式下运行 proc，这取决于它是否是故障排除的正常操作并获得更多或更少的输出。
In this particular case the primary stored proc will be run repeatedly from an agent job, but may be run from management studio when troubleshooting.
在这种特殊情况下，主要存储过程将从代理作业重复运行，但在故障排除时可能会从管理工作室运行。
The procs already use an error log table and email facilities to catch raised errors. The kind of thing I'm looking to be able to track down is where an input data issue means the output data is wrong but the procs don't fail completely, and it would be useful to see exactly what was done at each step.
procs 已经使用错误日志表和电子邮件工具来捕获引发的错误。我希望能够追踪的事情是输入数据问题意味着输出数据错误但过程不会完全失败，并且准确查看每一步所做的事情会很有用。
What approaches do you know of for producing meaningful and ideally filterable output?
您知道哪些方法可以产生有意义且理想情况下可过滤的输出？
One per answer so we can see the final rankings ;-)
每个答案一个，这样我们就可以看到最终排名;-)
Out of the box answers welcome, such as "don't - step through with management studio when you need to"
欢迎开箱即用的回答，例如“不要 - 在需要时通过管理工作室”
回答by Tim Abell
Piles of print statements
成堆的打印报表
eg
例如
print 'Doing something...'
INSERT INTO foo(a) VALUES(1)
print @@ROWCOUNT
This answer just for balance, seeing as it's quiet round here.
这个答案只是为了平衡，因为这里很安静。
回答by Martin Smith
One approach might be to check if the proc is being run from SSMS and fire some Custom user configurable SQL Server Profiler eventsif so. e.g.
一种方法可能是检查 proc 是否正在从 SSMS 运行，如果是，则触发一些自定义用户可配置的 SQL Server Profiler 事件。例如
CREATE PROC foo
AS
DECLARE @debug bit = CASE WHEN APP_NAME() = 'Microsoft SQL Server Management Studio - Query'
THEN 1
ELSE 0 END
DECLARE @userinfo nvarchar(128)
DECLARE @userdata varbinary(8000)
--Do some work here
IF @debug = 1
BEGIN
--Do some custom logging
SET @userinfo = N'Some custom info'
SET @userdata = CAST('Some custom data' AS varbinary(8000))
EXEC sp_trace_generateevent @eventid = 82 /*Use 82-91*/
,@userinfo = @userinfo
,@userdata = @userdata
END
Though in practice I usually use something like the below. Sometimes with additional code to log the message (and possibly step and status values) to a table depending on requirements. This would allow the "filterable" requirement to be met.
虽然在实践中我通常使用类似下面的东西。有时根据要求使用附加代码将消息（以及可能的步骤和状态值）记录到表中。这将允许满足“可过滤”要求。
This stops the message being printed out except if the APP_NAMEindicates it is being run in (an English language version of) SSMS and uses NOWAITso the message gets printed out immediately rather than waiting for the buffer to fill.
这将停止打印消息，除非APP_NAME指示它正在（英语版本的）SSMS 中运行并使用，NOWAIT以便立即打印出消息而不是等待缓冲区填满。
CREATE PROCEDURE [dbo].[PrintMessage] @message NVARCHAR(MAX),
@PrependCurrentTime BIT = 0
AS
IF APP_NAME() LIKE 'Microsoft SQL Server Management Studio%'
BEGIN
DECLARE @CurrentTimeString VARCHAR(30);
SET @CurrentTimeString = ''
IF @PrependCurrentTime = 1
BEGIN
SET @CurrentTimeString = CONVERT(VARCHAR(19), GETDATE(), 20) + ' '
END
RAISERROR('%s%s',0,1,@CurrentTimeString,@message) WITH NOWAIT
END
回答by Tim Abell
Don't add tracing output, instead just step through as needed with the sql server management studio debugger.
不要添加跟踪输出，而是根据需要使用 sql server management studio 调试器逐步完成。
(Added to see if people prefer this)
（添加以查看人们是否喜欢这个）
