我有以下存储过程:
ALTER PROCEDURE [dbo].[spTitle_GetTitleById]
(
@TitleId INT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT
Id,
Name,
Active
FROM
Title
WHERE
Id = @TitleId
END
如果我不想返回消息,我被告知要使用SET NOCOUNT ON;
。我通过 SQL Server Management Studio 2008 运行此存储过程,并收到以下消息:
(1 row(s) affected)
这仍然是一条消息。我们的一位 DBA 说情况确实如此,但是当它通过应用程序运行时,它不会返回任何消息。有没有一种方法可以测试我使用SET NOCOUNT ON;
时是否返回消息,我不想假设,我想知道。
我右键单击存储过程并选择“执行存储过程...”然后将其设置为“关闭”,结果是:
(1 row(s) affected)
(1 row(s) affected)
因此将其设置为“打开”或“关闭”仍然会在结果面板的“消息”选项卡中返回消息。
还有一个问题,什么时候(在什么情况下)使用 SET NOCOUNT OFF; 是明智的?
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;
?
发布评论
评论(3)
当过程退出并进入调用堆栈时,
SET NOCOUNT ON
会重置。当您从 SSMS 执行该过程时,它会生成如下所示的脚本。如果您出于某种原因想避免这种情况,则需要在外部批次中
SET NOCOUNT ON
。请参阅设置 NOCOUNT ON 使用,了解有关启用此ON或<代码>关闭
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.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 thisON
orOFF
请参阅什么是在 SQL Server 查询中关闭 NOCOUNT 的优点和缺点? 对于打开
SET NOCOUNT ON
的好处至于为什么要关闭它(以便返回行计数) - 你需要每当你希望能够知道在没有结果集的情况下有多少行受到影响,或者您希望能够在不首先读取整个结果集的情况下获得行计数。
例如,在 .Net 中,
DataAdapter
类使用行计数,因此设置NOCOUNT ON
会导致在编辑或删除数据时出现问题 (来源)。See What are the advantages and disadvantages of turning NOCOUNT off in SQL Server queries? For the benefits turning
SET NOCOUNT ON
As for why you would want to turn this off (so that rowcounts are returned) - you need this off whenever you want to be able to tell how many rows were affected in situations where there is no resultset, or you wish to be able to get a rowcount without first reading through the entire resultset.
For example in .Net the
DataAdapter
class uses rowcounts and so settingNOCOUNT ON
causes issues when editing or deleting data (source).这是不正确的,编写过程并确保它不是关闭而是打开,如果它是打开的,则不应返回(1行受影响)消息
此外,您如何执行过程
就是这样
That is not correct, script out the proc an make sure it is not OFF instead o ON, if it is ON it should not return (1 row(s) affected) messages
Also how are you executing the proc
is is just this