Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Unanswered: Error handling in TSQL

    I have the following codes in TSQL to run. I set the last insert statement to have error. But I did not get the rollback tran.

    What i got is:
    Here goes the transactions
    (10 rows)
    (12 rows)
    (13 rows)
    Server: Msg 208, Level 16, State 1, Line 6
    Invalid object name '#manageril1'.
    ======================================
    begin tran
    print 'Here goes the transactions'
    insert into profile select * from #profins
    insert into prof_compo select * from #compoins
    insert into apps_user select * from #appluserins
    insert into manager select * from #manageril1
    if @@error <> 0
    BEGIN
    ROLLBACK TRAN
    print 'rollback'
    Return
    END
    else
    Commit tran
    print 'commit tran'
    go
    =====================================
    What could I have not done or missed out? Why the rollback doesnot work?

    Please advice.

    Rgds,
    Sam.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    From BOL:

    A batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft® SQL Server™ for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.

    A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed.

    A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:

    Most run-time errors stop the current statement and the statements that follow it in the batch.

    A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.

    Some recommendations about your case:

    - check for errors after every insert (update,delete) statement;
    - create sp and check return value (rollback if needs).

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem isn't in your rollback. Your IF statement is never executed because the procedure fails critically before it gets to it. @@ERROR registers constraint and primary key violations, but if a gross syntax error causes the process to crash then its not going to help.

    blindman

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    And the error handling is incorrect
    @@error is reset by every sql statement. You might want to set a variable or goto an error handler on errror.

    begin tran
    print 'Here goes the transactions'

    insert into profile select * from #profins
    if @@error <> 0
    BEGIN
    ROLLBACK TRAN
    print 'rollback'
    Return
    END
    insert into prof_compo select * from #compoins
    if @@error <> 0
    BEGIN
    ROLLBACK TRAN
    print 'rollback'
    Return
    END
    insert into apps_user select * from #appluserins
    if @@error <> 0
    BEGIN
    ROLLBACK TRAN
    print 'rollback'
    Return
    END
    insert into manager select * from #manageril1
    if @@error <> 0
    BEGIN
    ROLLBACK TRAN
    print 'rollback'
    Return
    END
    Commit tran
    print 'commit tran'

Posting Permissions

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