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.

Answer 2

I know that having SET NOCOUNT ON would make a DataAdapter think there was a concurrency conflict.

You can read about it on MSDN. If the code is going to be used by DataAdapters then obviously don't use SET NOCOUNT ON.

It looks like SqlCommand also has this behaviour, which I guess is the reason why the DataAdapter has a problem (as under the hood it will use a Command object).

Answer 3

SET ROWCOUNT 0 -- is a simple way to clear previously set count

Answer 4

Don't use RETURN for values. By convention RETURN from stored procedures is for error codes, 0 meaning no error and non-0 meaning some kind of problem. If you need data back, the appropriate way to do it is with an OUTPUT parameter. It's a little counter-intuitive based on other languages' use of return.