问题描述
我正在 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.
这篇关于SET NOCOUNT OFF 还是 RETURN @@ROWCOUNT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！