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

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' a开发者_高级运维s 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.

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