Begin transaction ........... commit transaction

  • Hi,

    I have this stored procedure:

    create stored procedure test

    as

    Begin

    update table xtu set cod ='A'

    End

    if i execute this stored procedure from inside other stored procedure, like this:

    create procedure test2

    as

    BEGIN

    declare @erro as int

    begin transaction

    exec test

    set @erro =@@error

    if @erro =0

    begin

    commit transaction

    end

    else

    rollback transaction

    END

    If i get an error when executing the procedure test can i rollback the transaction that was made by the procedure test? or should i place a BEGIN TRANSACTION and COMMIT TRANSACTION inside the stored procedure test , so that i can rollback transaction from within procedure test2 if procedure test fails?

    Thank you

  • Look up Try Catch in BOL. That will help you with being able to rollback the transaction.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Check this, it should answer your question

    http://msdn.microsoft.com/en-us/library/ms189336(SQL.90).aspx

    EnjoY!
  • GTR (3/26/2010)


    Check this, it should answer your question

    http://msdn.microsoft.com/en-us/library/ms189336(SQL.90).aspx

    This method would allow the OP to place a transaction in both stored procedures (thus nest the transactions).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply