CODEWITHSUNDEEP
c#.netsql-server
SQL Police
SQL Police

Reputation: 4206

Rollback without throwing an exception?

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 ?

Upvotes: 2

Views: 794

Answers (1)

SundaraPandian
SundaraPandian

Reputation: 329

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

Upvotes: 1

Related Questions