19

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?

Charles
  • 50,943
  • 13
  • 104
  • 142
niaher
  • 9,460
  • 7
  • 67
  • 86

5 Answers5

26

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.

slugster
  • 49,403
  • 14
  • 95
  • 145
CJM
  • 11,908
  • 20
  • 77
  • 115
6

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.

onupdatecascade
  • 3,336
  • 22
  • 35
  • There's a reason for this: Last I checked, RETURN is limited to shortint values, ie, maximum value returnable is 65535. – enorl76 Aug 06 '12 at 23:50
  • Currently the RETURN value is INT rather than SHORTINT. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql I would still not recommend using it for anything other than error codes though. – Zusukar Jul 21 '20 at 18:47
4

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).

RichardOD
  • 28,883
  • 9
  • 61
  • 81
  • I don't see anything mentioned about SET NOCOUNT inside the SqlCommand documentation. Are you sure that there's the same problem as with DataAdapters? – niaher Jun 15 '09 at 11:52
  • 1
    Google it- you will find lots of evidence that it does: http://www.google.co.uk/search?client=firefox-a&rls=org.mozilla%3Aen-GB%3Aofficial&channel=s&hl=en&q=ExecuteNonQuery+NoCount&meta=&btnG=Google+Search. I can't be sure it's the same problem as DataAdapters without testing it, but I don't see why there would be another reason. – RichardOD Jun 15 '09 at 12:00
2

Reasons for using SET NOCOUNT ON/OFF:

To control the stack overflow while inserting rows into any table. Passing the T-Sql messages while executing of the queries or nested queries. To Show or viewing the latest queries executed. To get information on the latest record escalation.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Law Kant
  • 21
  • 1
-1

Why we use SET NOCOUNT on/off ---

Ans : we can understand this by following steps

step 1 : execute query "Select top 10 * from table name".

step 2 : open message window it shows a message "10 rows affected". it creates extra overheads and extends our execution time.

step 3 : to overcome this extra overheads we use SET NOCOUNT ON. If it is On then it will never count the number of row returns instead it sows a message commands completed successfully.

step 4 : By default NOCOUNT is ON then it counts the number of returned rows that is why my suggestion that it should off during creating new procedures to get better performance from database server.

Ashutosh
  • 1
  • 1