Insertion fails in transaction, but sql server returns 1 row (s)?

Question

This is the thread of my stored procedure :

 ALTER procedure dbo.usp_DoSomething as declare @Var1 int declare @Var2 int declare @Var3 int select @Var1 = Var1, @Var2 = Var2, @Var3 = Var3 from Table where ... BEGIN TRY BEGIN TRANSACTION /* UPDATE Table. This executes successfully */ /* INSERT Table. This fails due to PK violation */ COMMIT TRAN /* This does not happen */ END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN /* This occurs because TRANS failed */ END CATCH 

Running UPDATE . INSERT fails, so transaction discarded.

After execution, the table looks right and nothing has changed. But when I start SP, I get the following messages:

 (1 row(s) affected) (0 row(s) affected) 

So, I ask myself: where did the first 1 row(s) affected come from?

Then I think this is the reason, but I wanted to confirm: OUTPUT clause (Transact-SQL)

 An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement. 

Answer 1

By default, a string will be returned for each DML instruction unless SET NOCOUNT ON is activated. Regardless of whether the transaction was successful or not, or rollback or transfer, your UPDATE statement was successful, so the notification (1 row(s) affected) .

The phrase OUTPUT you quoted has nothing to do with it, because you did not specify it.

Answer 2

The first choice with the settings may lead to the 1st row affected