问题描述
我正在 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.