如何在存储过程运行时使用PRINT语句跟踪执行？ [英] How to use the PRINT statement to track execution as stored procedure is running?

问题描述

我有一个带有while循环的存储过程，我想在每次循环后打印一些消息...程序执行成功....但我想在每个循环后打印消息...

I have a stored procedure with a while loop in it and I want some messages to be printed after every loops...Procedure Executed Successfully....But I want to Print Message after every Loop...

Create Procedure [dbo].[XXXX_SP]
(
@XXX DateTime,
@YYY varchar(10) )
As
Begin
Set @TotCnt=ceiling(@TotCnt)
    While @Cnt= @TotCnt
    Begin
	Print ''File Processing Started ''+Str(@Cnt)
Execute XXXXXX  ()       
 Set @SqlQry =Select  b.Zone,b.Division,a.* into  from test a left join final b on a.[aaa]=b.aaa
     Exec (@SqlQry)
     Set @Cnt=@Cnt+1
    End
End

推荐答案

而不是使用 PRINT 使用RAISEERROR的一个名为 NOWAIT 的功能，例如

Instead of using PRINT use a feature of RAISEERROR called NOWAIT e.g.

RAISERROR 'File Processing Stated ' + Str(@Cnt), 0, 1) WITH NOWAIT

使用 0 表示sql将错误视为简单的消息。



这些链接提供更多详细信息

http://www.mssqltips.com/sqlservertip/1660/ using-the-nowait-option-with-sql-server-raiserror-statement / [ ^ ]

http://msdn.microsoft.com/en-us/library/ms178592.aspx [ ^ ]

http://www.sommarskog.se/error-handling-I.html [ ^ ]



- 我的另一种技巧过去使用的是在同一个数据库上有一个日志表...在这个例子中用替换 print 插入到logtable中值（''文件处理开始''+ Str（@Cnt）） - 并考虑每次清除表格或使用自动生成时间戳列

Using the 0 means that sql treats the "error" like a straightforward message.

These links give more details
http://www.mssqltips.com/sqlservertip/1660/using-the-nowait-option-with-the-sql-server-raiserror-statement/[^]
http://msdn.microsoft.com/en-us/library/ms178592.aspx[^]
http://www.sommarskog.se/error-handling-I.html[^]

- Another technique I''ve used in the past is to have a "logging table" on the same database ... in this instance replace the print with insert into logtable values(''File Processing Started'' + Str(@Cnt)) - and consider either clearing the table down each time or using having timestamp column autogenerated

这篇关于如何在存储过程运行时使用PRINT语句跟踪执行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！