Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    22

    Question Unanswered: Rolling back multiple transactions......how to?

    I have a stored proc that is executed from within another stored proc, both of these procs run transactions in them that update data.

    Now if either of these 2 SPs fail i want to be able to roll back the transactions that have occured so that the data doesn't change.

    Is this possible? if so, how?

    Thanks.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by sconly View Post
    i have a stored proc that is executed from within another stored proc, both of these procs run transactions in them that update data.

    Now if either of these 2 sps fail i want to be able to roll back the transactions that have occured so that the data doesn't change.

    Is this possible? If so, how?

    Thanks.
    begin transaction
    some sql statement such as insert
    check for error
    if error
    rollback transaction
    else
    commit transaction

  3. #3
    Join Date
    Oct 2005
    Posts
    22
    Isn't that just for 1 proc/transaction at a time?

    I'm saying that proc 1 runs a transaction, then execs proc 2 and if the transaction in proc 2 fails then roll back proc 1s transaction. and vice versa.

    thanks

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by sconly View Post
    Isn't that just for 1 proc/transaction at a time?

    I'm saying that proc 1 runs a transaction, then execs proc 2 and if the transaction in proc 2 fails then roll back proc 1s transaction. and vice versa.

    thanks
    Not necessarily, what you want is a wrapper which is what you described. The called sproc can have the same transaction structure but on error also break and exit .... and so on....

  5. #5
    Join Date
    Oct 2005
    Posts
    22
    What do you mean by a wrapper?

    Could you give some sample code, please?

    Thanks

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by sconly View Post
    What do you mean by a wrapper?

    Could you give some sample code, please?

    Thanks
    The wrapper is a stored procedure which calls other stored procedures like below code snippet. The called sproc has trans, error checking, commit or rollback logic. When finished executing the return code is passed to called program step. If an error exist then program execution goto err_handler which processes some event or report. It is possible to have a begin trans block that does the same in the wrapper, but I'd do it at the individual sproc's.

    Code:
    exec sp_get_account_data
    
    SELECT @err = @@error IF @err <> 0
    BEGIN
    GOTO err_handle
    END
    
    exec sp_get_account_address_data
    
    SELECT @err = @@error IF @err <> 0
    BEGIN
    GOTO err_handle
    END
    
    exec sp_get_adjustments
    
    SELECT @err = @@error IF @err <> 0
    BEGIN
    GOTO err_handle
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •