sql - 更新语句 - 捕获错误并继续其余的

Question

我有大约 2000 个 SQL 更新命令要运行，我知道其中一些命令可能会因各种原因而失败。我想全部或分批运行它们，并为那些失败的失败捕获失败，同时继续浏览列表的其余部分。

我正在研究的两种方法是:
XACT Abort

set xact_abort on
begin transaction
 -- Updates here --
commit transaction

Try catch

BEGIN TRY
    -- Updates here --
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH

这些的问题在于，当它们失败时，它们就会失败(至少，这是我测试中发生的情况)。然后我必须用它失败的那条线路解决问题并重新启动它。我想忽略并记录任何失败然后继续。这是否可以使用 xact_abort或 try catch键入查询还是我应该查看其他内容？

Answer 1

这是类似于我使用的基于的内容:Error and Transaction Handling in SQL Server Part One (of Three) – Jumpstart Error Handling - Erland Sommarskog

表和程序设置:

create table dbo.error_handler_log (
    id int identity(1,1) not null primary key
  , error_date datetimeoffset(7) not null
  , severity tinyint not null
  , [state] tinyint not null
  , [number] int not null
  , line int not null
  , [procedure] sysname null
  , message nvarchar(2048) 
);
go
create procedure [dbo].[error_handler_sp] as 
begin 
  set nocount, xact_abort on;
  declare
      @error_date datetimeoffset(7) = sysdatetimeoffset()
    , @severity tinyint = isnull(error_severity(),16)
    , @state tinyint = isnull(error_state(),1)
    , @number int = isnull(error_number(),0)
    , @line int = isnull(error_line(),0)
    , @procedure sysname = error_procedure()
    , @message nvarchar(2048) = error_message();
  insert into [dbo].[error_handler_log] 
    ([error_date],[procedure],[severity],[state],[number],[line],[message]) values 
    (@error_date, @procedure, @severity, @state, @number, @line, @message);
  --raiserror(@message, @severity, @state); /* don't re-raise error to continue code execution */
end;
go

雷克斯特演示: http://rextester.com/EYLAFM93158

dbfiddle.uk demo

begin try;
  select 1/0 as err;
end try
begin catch;
  exec dbo.error_handler_sp;
end catch;
begin try;
  select 1/0 as err;
end try
begin catch;
  exec dbo.error_handler_sp;
end catch;
begin try;
  select 1/1 as one;
end try
begin catch;
  exec dbo.error_handler_sp;
end catch;

select *
from dbo.error_handler_log;

返回:

+-----+
| one |
+-----+
|   1 |
+-----+

+----+----------------------------+----------+-------+--------+------+-----------+-----------------------------------+
| id |         error_date         | severity | state | number | line | procedure |              message              |
+----+----------------------------+----------+-------+--------+------+-----------+-----------------------------------+
|  1 | 10/08/2017 13:26:00 +01:00 |       16 |     1 |   8134 |    2 | null      | Divide by zero error encountered. |
|  2 | 10/08/2017 13:26:00 +01:00 |       16 |     1 |   8134 |    8 | null      | Divide by zero error encountered. |
+----+----------------------------+----------+-------+--------+------+-----------+-----------------------------------+