locked
how to use @@erro to rollback transactions RRS feed

  • Question

  • How can i use @@erro to rollback failed statements/transactions!??!
    Monday, March 2, 2009 1:39 PM

Answers

  • Something like:

    begin transaction;

    update ...

    select @error = @@error, @rc = @@rowcount;

    if @error <> 0
    begin
        ...
        goto ErrorHandler
    end

    ...

    commit transaction;

    return 0;

    ErrorHandler:

    if @@trancount > 0
       rollback transaction;

    ...

    return -1;


    You need to inquiry about @@error after each dml statement, mainly ins, upd, del, create table, etc.

    If you are working with SQL Server 2005 / 2008, then check BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH block, which is the new way to catch errors.

    AMB
    Monday, March 2, 2009 1:57 PM