Tell SQL Server the error is "handled" in try...catch

Question

I'd like to indicate to SQL Server 2005, in my BEGIN CATCH...END CATCH block that the error is "handled"... That is, clear the error.

Is that possible? Consider this:

begin transaction 
  begin try 
    begin transaction 

      select cast('X' as bit) 
    commit transaction 
   end try 
 begin catch rollback transaction 

   select error_number(), error_message() 
 end catch 

 commit transaction

This results in the following:

(0 row(s) affected)

(No column name)    (No column name)
245 Conversion failed when converting the varchar value 'X' to data type bit.

(1 row(s) affected)
Msg 3902, Level 16, State 1, Line 13
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Thanks. A.

You've got a redundant transaction declared - remove the outermost transaction declaration. — OMG Ponies
– OMG Ponies, Commented May 20, 2010 at 0:00
Ah - thanks again for your quick response! :) But I want it... the outer transaction, I mean... The try-catch is actually in an loop and I want whatever didn't fail to be committed in the end... — Restless Archer
– Restless Archer, Commented May 20, 2010 at 0:03
In fact, if I don't start or rollback any transactions in the T-SQL (in a stored proc) but start one in my application (c#), the call ultimately fails with the error [The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.] — Restless Archer
– Restless Archer, Commented May 20, 2010 at 0:07

Remus Rusanu · Accepted Answer · 2010-11-20 08:06:53Z

Not all errors are maskable. You are always supposed to inspect the XACT_STATE() and see if you can continue. Certain errors (1205 deadlock being a typical example) will rollback the transaction and not allow you to continue.

What you describe (a loop which can preserve the work) is ussualy done with the help of a savepoint:

begin transaction
begin try
while @loopcondition
begin
   save transaction loop;
   begin try
      -- process loop element here
   end try
   begin catch
     if xact_state() = -1
     begin
        -- whole transaction is doomed
        rollback;
        raiserror ('Aborting', ....);
     end
     else if xact_state() = 0
     begin
        -- trasaction was aborted by inner loop
        raiserror ('Aborted inside', ....);
     end  
     else if xact_state() = 1
     begin
       -- this error is recoverable, rollback to the savepoint and continue the loop
       rollback loop
     end
   end catch 
   -- continue loop here
   fetch next from ....
/*   
   -- batch commit here if batch committing
   if @batchsize 
   begin
      commit;
      begin transaction 
   end
*/
end
commit;
end try
begin catch
  -- if we get here, we could not handle the error inside the loop and continue
  if xact_state() != 0
    rollback
  raiserror('failed to process', ...)
end catch

Collectives™ on Stack Overflow

Tell SQL Server the error is "handled" in try...catch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related