sql - 在 SQL Server 中运行查询时忽略或处理错误

Question

当我通过 SQL Server Mgmt Studio 执行批量插入查询时，它会产生以下错误，但会完成其他查询的插入过程。

Msg 18054, Level 16, State 1, Procedure X, Line 14
Error 50001, severity -1, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Msg 18054, Level 16, State 1, Procedure Y, Line 14
Error 50001, severity -1, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Msg 18054, Level 16, State 1, Procedure Z, Line 14
Error 50001, severity -1, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

但是，当通过终端中的 ant 作业执行插入查询时，进程会停止并显示以下错误消息。

BUILD FAILED
C:\test\mssql\build.xml:27: The following error occurred while executing this line:
C:\test\mssql\build-core.xml:74: com.microsoft.sqlserver.jdbc.SQLServerException: Error 50001, 
severity -1, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, 
make sure the user-defined message is added using sp_addmessage.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

我试图通过忽略它来处理错误:

set ANSI_WARNINGS OFF;

并且通过为抛出错误的过程定义 try-catch block :

BEGIN TRY  
    RAISERROR (50001,-1,1, 'X');
    RAISERROR (50001,-1,1, 'Y');
    RAISERROR (50001,-1,1, 'Z');
END TRY  
BEGIN CATCH  
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
        @ErrorMessage = ERROR_MESSAGE(),  
        @ErrorSeverity = ERROR_SEVERITY(),  
        @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );  
END CATCH;  

但是由于构建失败，错误仍然存​​在于终端中。

如果我如下定义自定义错误消息:

EXEC sp_addmessage 50001, 16, N'Test Message'; 
GO

我刚刚收到上面的错误消息而不是 ，但是在 sys.messages 中没有找到具有该错误编号的消息。如果错误大于 50000，请确保使用 sp_addmessage 添加了用户定义的消息。

有什么方法可以处理抛出错误的过程，并像 SQL 客户端那样继续插入直到结束。

Answer 1

进一步思考您必须要问的问题，我认为您的问题的答案是:

您需要将每条可能导致您希望忽略的错误的语句放入其自己的 TRY..CATCH block 中。在每个 catch block 中，您可以根据错误消息决定是将其抛出并停止处理，还是什么都不做，将其存储在一个变量中，然后继续。

然后在一切结束时，您可以检查您的变量，看看是否有任何您忽略的错误，并在那个时候抛出它们(如果您想要的话)，而无需回滚已经运行的语句成功。