sql-server-2005 - 在try…catch中告诉SQL Server错误是 “handled”

Question

我想在我的BEGIN CATCH ... END CATCH块中向SQL Server 2005指示该错误已“处理” ...即清除该错误。

那可能吗？考虑一下:

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 

结果如下:

(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.

谢谢。
一种。

Answer 1

并非所有错误都是可以掩盖的。您总是应该检查XACT_STATE()并查看是否可以继续。某些错误(1205死锁是一个典型示例)将回滚事务，并且不允许您继续。

您所描述的(可以保留工作的循环)通常是在保存点的帮助下完成的:

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