SQL Server Performance

Transaction not rolling back

Discussion in 'General Developer Questions' started by KiwiinAussie, Mar 18, 2007.

  1. KiwiinAussie New Member

    I have a stored procedure that calls another stored procedure with the first stored procedure opening a transaction:

    BEGIN
    SET XACT_ABORT ON

    BEGIN TRANSACTION

    does various updates/inserts

    calls 2nd stored procedure to proccess updates/inserts common to many other stored procedures

    does more various updates/inserts

    commit

    END

    The problem I'm having is that within the 2nd stored procedure is that if it encounters an error, it does not roll back the entire transaction and I finish up with missing records in the database. Am using this in the 2nd stored procedure:

    if(@TypeId1 = @TypeId2 and @Line1 <> '' and @Line2 <> '')
    begin
    RAISERROR('error message', 16, 1)
    RETURN
    end

    What could the problem be? From what I've read, it seems as though you can't have an open transaction within one sp that calls another sp and it maintains the same transactoin? Is this corrrect?

    I tired the following too, and I still couldn't get it to work. Any ideas anyone?

    ************ sp 1 ***********

    Declare @AddressError char(3)

    SET XACT_ABORT ON
    BEGIN TRANSACTION

    exec Sp2
    @AddressError OUTPUT,
    @variable1,
    @variable2,
    etc. etc

    ************** sp 2 *****************

    @AddressError char(3) OUTPUT,

    if(@TypeId1 = @TypeId2 and @Line1 <> '' and @Line2 <> '')
    begin
    RAISERROR('error message', 16, 1)
    RETURN
    end

    SET XACT_ABORT ON
    BEGIN TRANSACTION

    process updates/inserts

    Set @AddressError = 'no'
    Commit

    ******** back to sp 1************

    If @AddressError <> 'no'
    BEGIN
    rollback transaction
    END

    continue doing updates/inserts

    commit
  2. merrillaldrich New Member

Share This Page