Robert Harvey Robert Harvey - 1 year ago 50
SQL Question

Do I have to include "SELECT @@RowCount" if I have more than one SQL statement?

I know that, if I execute a single SQL statement that UPDATEs or DELETEs some data, that it will return the number of rows affected.

But if I have multiple SQL statements in a sql script, and I want to know the number of rows affected from the last statement executed, will it still return that automatically, or do I need a

SELECT @@RowCount

at the end of the script?

The code in question is not a Stored Procedure. Rather, it is a parameterized SQL script stored in an arbitrary location, executed using the
function in Entity Framework, as in:

var numberOfRowsAffected = context.ExecuteStoreCommand<int>(mySqlScript, parameters);

Answer Source

It depends on the NOCOUNT setting when executing your quer(y/ies).

If NOCOUNT is ON then no DONE_IN_PROC messages will NOT be returned.

If NOCOUNT is OFF, the default setting, then DONE_IN_PROC messages will be returned, (eg. counts).

Both of these situations are different to executing,


which will return a result set with a single scalar value, different from a DONE_IN_PROC message. This will occur, regardless of the setting of NOCOUNT.

I believe that SELECT @@ROWCOUNT is sometimes used to make Entity Framework "play" with more complex TSQL statements because EF both requires

  1. Requires a count for post validation
  2. And will accept a scalar number result set as a substitute for a DONE_IN_PROC message.

Its important that SELECT @@ROWCOUNT; is executed immediately after the last query statement because many statements will reset @@ROWCOUNT and therefore yield an unexpected result.