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.
@@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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With