title: SET NOCOUNT OFF or RETURN @@ROWCOUNT?
body: <p>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?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I know that both work, but which is a better choice and why?</p>
  
  <hr>
  
  <p>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?</p>