0

I used to count the number of affected rows using the @@ROWCOUNT. 

DECLARE @TotCount INT

DELETE * FROM TabA WHERE A = 'a'
SET @TotCount = @TotCount + @@ROWCOUNT

DELETE * FROM TabB WHERE B = 'b'
SET @TotCount = @TotCount + @@ROWCOUNT

UPDATE TabC SET C = 'c' WHERE C='d'
SET @TotCount = @TotCount + @@ROWCOUNT

But think of a situation, where the script has many DELETE and UPDATE statements. Is there a way to count the number of all affected rows at once?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Noor A Shuvo
  • 2,639
  • 3
  • 23
  • 48
  • You can `insert` value of `@@ROWCOUNT` into a `temp table`, then return it using `select` statement. – csharpbd Apr 18 '17 at 09:15
  • Please check the answer of @Nico in [this question](http://stackoverflow.com/questions/42542356/asp-net-sql-server-stored-procedure-return-message/42647179#42647179). – csharpbd Apr 18 '17 at 09:16
  • @csharpbd- I want to get the number of affected rows at once at the end of the script – Noor A Shuvo Apr 18 '17 at 09:27
  • Yes, I got it, but you can't get it by using a single statement. So, you have to find other way. That's why I've suggested the answer. – csharpbd Apr 18 '17 at 09:32
  • There are others questions like your question. [return-number-of-rows-affected-by-update-statements](http://stackoverflow.com/questions/1103260/return-number-of-rows-affected-by-update-statements). Check this maybe it will help you. – csharpbd Apr 18 '17 at 09:34
  • You can count the rows before and after the all DELETEs and get the difference. For the UPDATES you should change the state of some counter/flag column and check the difference before/after. Not really clean, but easy to implement. – MarcM Apr 18 '17 at 09:34
  • @NoorAShuvo Is there a ***real*** reason to get such a count? I emphasise "real" because I don't see much value in it. `@@ROWCOUNT` is an efficient way to determine how many rows were affected in a _single_ operation without worrying about the _transactional issues_ of using a second statement to get that information. If you're performing multiple operations, you would have to control any required transactions explicitly in any case. You could easily implement each operation in a stored proc that takes an additional @CumulativeRowCount as an in/out parameter. – Disillusioned Apr 18 '17 at 11:11
  • ***But first*** I suggest you ask yourself why you need it (remember you're dealing with _separate tables_ that should be _atomically independent_). Keep asking "Why?"; look for the ***real business value***. If the best you can come with "it will be nice to have", then dump the feature. It adds unnecessary complexity and please remember the [YAGNI](https://en.wikipedia.org/wiki/You_aren%27t_gonna_need_it) principle. – Disillusioned Apr 18 '17 at 11:12

1 Answers1

0

Not that I have ever found. @@ROWCOUNT is always the number of rows affected by the last statement, and you can only delete from one table at a time.

There is a command called MERGE that allows for insert/update/delete to be executed in one go, but again it can only affect one table at a time.

SpaceUser7448
  • 189
  • 1
  • 10