Rollback without throwing an exception?

Question

On SQL Server 2008r2, we have a stored procedure which looks like this:

create procedure x(@arg1 as nvarchar(20), @arg2 ... )
as
begin
  -- delete and insert values, depending on arguments
   begin transaction;
   delete from tblA where f1 = @arg1 ... ; 
   insert into tblB (a) select ... where f2 = @arg2 ... ; 
   commit transaction;
end;

I call this procedure in C# (.NET 4.5) with the SqlCommand.ExecuteNonQuery() Method. All exceptions are caught with try--- catch

Now in the documentation to this method, it says "If a rollback occurs, the return value is -1."

Question: Could it be the case that a rollback occurs without getting an exception?

So far, we always got an exception, if the sql statement could not be executed. But could there be the case that a rollback occurs "automatically" without throwing an exception ?

hummm - I think depending on the XACT_ABORT setting, you could get to a state whereby Delete is honored but the insert isn't. — Mike Miller, Oct 02 '13 at 18:54
@MikeMiller And in that case, the procedure would simply rollback, and not throw an exception ? — SQL Police, Oct 03 '13 at 05:10
If not set correctly, the process would throw an exception and roll back the Insert only. — Mike Miller, Oct 04 '13 at 07:54

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

Can you please take a look on gbn's answer, transaction template Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Based on you question, I have modified the procedure to return -1 when rollback occurs.

CREATE PROCEDURE [Name]
    @arg1 as nvarchar(20), 
    @arg2 as nvarchar(20)
AS
    SET XACT_ABORT, NOCOUNT ON

    DECLARE @starttrancount int

    BEGIN TRY
        SELECT @starttrancount = @@TRANCOUNT

        IF @starttrancount = 0
            BEGIN TRANSACTION

         delete from tblA where f1 = @arg1 ... ; 
         insert into tblB (a) select ... where f2 = @arg2 ... ; 

        IF @starttrancount = 0 
            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 AND @starttrancount = 0 
        BEGIN
           ROLLBACK TRANSACTION
           RETURN -1
        END  
    END CATCH
    GO

edited May 23 '17 at 12:14

Community

1
1

answered Oct 08 '13 at 08:42

SundaraPandian

317
1
2
7

Probably there is a misunderstanding. I want to keep my procedure as it is. I call it with ExecuteNonQuery(). I just wanted to know, if in this constellation it COULD happen, that ExecuteNonQuery() will return -1 without throwing an exception. – SQL Police Oct 11 '13 at 12:50
Yes I hope so. because if no rows affected it'll return -1. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1. Source: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx – SundaraPandian Oct 14 '13 at 02:39
I think these 2 links will Help http://stackoverflow.com/questions/10784830/why-executenonquery-returning-1-without-any-exception-and-without-inserting-t http://stackoverflow.com/questions/1833539/executenonquery-doesnt-throw-exception-on-insert – SundaraPandian Oct 14 '13 at 02:49

Rollback without throwing an exception?

1 Answers1