Return the total rows affected by SQL Transaction

Question

I have the following code in sql:

SET XACT_ABORT ON
Begin Transaction
INSERT INTO TABLE_A VALUES(/*Some Values*/)
INSERT INTO TABLE_B VALUES(/*Some Values*/)
INSERT INTO TABLE_C VALUES(/*Some Values*/)

Update Table Set Values A = A WHERE id = @id /* Some thing like that*/
Commit Transaction

So, i just wanted to know the total number of rows affected by in my Transaction Block of Insert and Updte statement

Solution

You can use @@ROWCOUNT variable

To get Inserts + all affected rows of update , declare a variable and store rowcount values in it.

DECLARE @totalRows INT
SET @totalRows = 0
INSERT INTO TABLE_A VALUES(/*Some Values*/)
SET @totalRows =@totalRows + @@ROWCOUNT

INSERT INTO TABLE_B VALUES(/*Some Values*/)
SET @totalRows =@totalRows + @@ROWCOUNT

INSERT INTO TABLE_C VALUES(/*Some Values*/)
SET @totalRows =@totalRows + @@ROWCOUNT

Update Table Set Values A = A WHERE id = @id /* Some thing like that*/
SET @totalRows =@totalRows + @@ROWCOUNT

SELECT @totalRows As TotalRowsAffected