SET NOCOUNT ON带回SQL Server Management Studio中的消息 [英] SET NOCOUNT ON brings back messages in SQL Server Management Studio

问题描述

我有以下存储过程：

  ALTER PROCEEDURE [dbo]。[spTitle_GetTitleById] 
（
 @TitleId INT 
）
 
 AS 
 
 BEGIN 
 
设为NOCOUNT ON； 
 
选择
 ID，
名称，
有效
从
标题
 WHERE 
 ID = @TitleId 
 
 END 
  

有人告诉我使用 SET NOCOUNT ON; 如果我不希望返回邮件。我通过SQL Server Management Studio 2008运行了此存储过程，并收到以下消息：

 （受影响的1行） 
  

这仍然是一条消息。我们的一位DBA表示会发生这种情况，但是当它通过应用程序运行时，它不会返回任何消息。当我使用 SET NOCOUNT ON时，是否可以测试是否返回了消息； 我不想假设，我想知道。 / p>

我右键单击存储过程并选择Execute Stored Procedure ...然后将其设置为OFF，然后得到：

 （受影响的1行）
（受影响的1行）
  

因此将其设置为ON或OFF仍会在结果面板的消息选项卡中带回消息。

问题，什么时候使用 SET NOCOUNT OFF; ？

SET NOCOUNT ON 在该过程退出并在调用堆栈上移时重置。当您从SSMS执行该过程时，它将生成如下脚本。

  DECLARE @return_value int 
 
 EXEC @return_value = [dbo]。[spTitle_GetTitleById] 
 @TitleId = 1 
 
 SELECT'Return Value'= @return_value / *消息来自此处* / 
  

如果出于某种原因想要避免这种情况，则需要 NOCOUNT ON 在外部批处理中。有关使用的优点的一些讨论，请参见设置NOCOUNT ON用法。开启或关闭

I have the following stored procedure:

ALTER PROCEDURE [dbo].[spTitle_GetTitleById]
(
   @TitleId INT
)

AS

BEGIN

   SET NOCOUNT ON;

   SELECT
      Id,
      Name,
      Active
   FROM
      Title
   WHERE
      Id = @TitleId

END

I was told to use SET NOCOUNT ON; if I don't want messages to be returned. I ran this stored procedure through SQL Server Management Studio 2008 and I got the following message:

(1 row(s) affected)

This is still a message. One of our DBAs said that this will be the case, but when it is run through an application it will not return any messages. Is there a way that I can test to see if messages were returned or not when I use SET NOCOUNT ON; I don't want to assume, I want to know.

I right clicked the stored procedure and selected Execute Stored Procedure... I then set it to OFF, and I got:

(1 row(s) affected)
(1 row(s) affected)

So setting it to ON or OFF it still brought back messages in the Messages tab in the results panel.

Just another question, when will it be wise (in what scenarios) to use SET NOCOUNT OFF;?

解决方案

SET NOCOUNT ON is reset when the procedure exits and it goes up the call stack. When you execute the procedure from SSMS it generates a script like the following.

DECLARE @return_value int

EXEC    @return_value = [dbo].[spTitle_GetTitleById]
        @TitleId = 1

SELECT  'Return Value' = @return_value  /*Message comes from here*/

If youi wanted to avoid that for some reason you would need to SET NOCOUNT ON in the outer batch. See SET NOCOUNT ON usage for some discussion about the merits of having this ON or OFF

这篇关于SET NOCOUNT ON带回SQL Server Management Studio中的消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！