CODEWITHSUNDEEP
sqlsql-servert-sql
HarshSharma
HarshSharma

Reputation: 660

Return the total rows affected by SQL Transaction

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

Upvotes: 11

Views: 15289

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

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

Upvotes: 14

Related Questions