SET NOCOUNT OFF or RETURN @@ROWCOUNT?

Question

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?

---

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?

Answer 1

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

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.