Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert fails within transaction, but sql server returns 1 row(s) affected?

This is the execution flow 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

The UPDATE runs successfully. The INSERT fails, so the transaction is rolled back.

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

(1 row(s) affected)

(0 row(s) affected)

So I'm asking myself, where is the first 1 row(s) affected coming from?

Then I'm thinking that this is the reason, but 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.
like image 528
fdkgfosfskjdlsjdlkfsf Avatar asked Apr 18 '16 15:04

fdkgfosfskjdlsjdlkfsf


People also ask

How do you check the rows affected as part of previous transactions?

@@ROWCOUNT – Get the Number of Rows Affected by the Last Statement in SQL Server. In SQL Server, you can use the @@ROWCOUNT system function to return the number of rows affected by the last T-SQL statement. For example, if a query returns 4 rows, @@ROWCOUNT will return 4.

Does SQL INSERT return anything?

An SQL INSERT statement writes new rows of data into a table. If the INSERT activity is successful, it returns the number of rows inserted into the table. If the row already exists, it returns an error.

What is @@ rowcount?

SQL Server @@ROWCOUNT is a system variable that is used to return the number of rows that are affected by the last executed statement in the batch.


1 Answers

By default, a rowcount will be returned for every DML statement, unless SET NOCOUNT ON is enabled. Regardless whether a transaction is successful or not, or rolled back or committed, your UPDATE statement was successful, thus the notification (1 row(s) affected).

The OUTPUT clause you mentioned has nothing to do with it, since you haven't specified it.

like image 71
Greg Avatar answered Oct 22 '22 00:10

Greg