SET NOCOUNT OFF 还是 RETURN @@ROWCOUNT?

2021-09-10 00:00:00 sql tsql sql-server rowcount nocount

我正在 Sql Server 2008 数据库中创建一个存储过程.我想返回受影响的行数.SET NOCOUNT OFF 或 RETURN @@ROWCOUNT 哪个更好?

I am creating a stored procedure in Sql Server 2008 database. I want to return the number of rows affected. Which is a better option SET NOCOUNT OFF or RETURN @@ROWCOUNT?

ALTER PROCEDURE [dbo].[MembersActivateAccount]
    @MemberId uniqueidentifier
AS
BEGIN
    -- Should I use this?
    SET NOCOUNT OFF;

    UPDATE [dbo].Members SET accountActive = 1 WHERE id = @MemberId;
    --Or should I SET NOCOUNT ON and use the following line instead?
    --return @@ROWCOUNT;
END

我知道两者都有效，但哪个更好，为什么?

I know that both work, but which is a better choice and why?

经过一些尝试，我得出的结论是，在存储过程中，默认情况下 SET NOCOUNT 为 OFF.是否可以在我的数据库中更改此行为?

After some trying I am coming to a conclusion that SET NOCOUNT is OFF by default inside stored procedures. Is it possible to change this behavior inside my database?

推荐答案

使用@@RowCount.它是明确和透明的，它完全由您的代码而不是内置行为控制.

Use @@RowCount. It's explicit and transparent, it is entirely controlled by your code rather than a built-in behaviour.

NOCOUNT 选项可以手动设置为默认为 ON (Optons>Query Execution>SQL Server>Advanced).如果您以这种方式设置它，但随后在您的存储过程中声明 SET NOCOUNT OFF，则该本地设置优先.

The NOCOUNT option can be manually set to default to ON (Optons>Query Execution>SQL Server>Advanced). If you set it this way but then declare SET NOCOUNT OFF in your stored procedure then that local setting takes precedence.

相关文章