存储过程Try/Catch中的错误处理

在默认自动提交模式下的单语句存储过程中,无需启动显式事务或指定 SET XACT_ABORT ON 。运行时错误将回滚语句所做的任何更改,并且错误将返回给客户端,而无需其他代码。

在多语句过程中(如 EXEC 和 MERGE 在您的问题中),显式事务将确保所有或无行为,允许您在成功时提交事务,或在发生错误时回滚。添加结构化错误处理可确保 TRY 出现错误后,块不会继续,并且 CATCH 块提供了一个方便的地方来集中错误处理,通常在需要时回滚事务并重新引发错误。

SET NOCOUNT ON 禁止将DONE\u IN\u PROC(rowcount)消息返回到不需要或不需要它们的客户端。对于一些需要额外编程来处理这些额外结果的API,如ADO classic(而非ADO.NET),这一点尤为重要。

将XACT\U ABORT设置为ON 确保在发生错误或客户端超时后回滚事务。当客户端超时时,客户端API会向停止执行查询发送取消请求,以便在SQL Server取消批处理时不会执行后续代码,包括CATCH块。 将XACT\U ABORT设置为ON 在这种情况下,将立即回滚事务。

下面是一个结构化错误处理示例。我没有打电话 dbo.usp_get_error_info 因为我不知道它是干什么的。 THROW 将重新引发原始错误。

ALTER PROCEDURE [dbo].[UspSdtSync]
AS
SET NOCOUNT ON; --suppress row count messages if not needed
SET XACT_ABORT ON; --ensure transaction is rolled back immediately after timeout

DECLARE                    @return_value INT
                          ,@RetCode INT
                          ,@RunID INT
                          ,@IntraDayID INT;

SET                        @RunID = NULL;  
SET                        @IntraDayID = NULL;

BEGIN TRAN;

EXEC                       @return_value = [DST].[SD].[STG].[API_GenerateTempView]
                           @SchemaName = N'TEST_A',
                           @ViewName = N'vw_TEST_KB_CGSE',
                           @ColumnList = N'statusE, statusF, statusG, statusH, LastModifiedDate, LastModifiedBy, LastReviewedBy, statusI, statusJ, Email, Mobile, HomePhone, WorkPhone, statusK, statusL, Dob',
                           @OrderByList = NULL,
                           @ResultSet = 1,                           
                           @RunID = @RunID,
                           @IntraDayID = @IntraDayID,
                           @RetCode = @RetCode OUTPUT;

MERGE INTO AeoiSdtTemp AS t
USING (SELECT statusE, statusF, statusG, statusH, LastModifiedDate, LastModifiedBy, LastReviewedBy, statusI, statusJ, Email, Mobile, HomePhone, WorkPhone, statusK, statusL, Dob 
FROM [DST].[SD].[TEST_KB_KTA].[vw_SDT_TEST_KB_CGSE_Temp]) AS s ON ( t.statusE = s.statusE) AND (t.statusF = s.statusF) AND (t.statusG = s.statusG) AND (t.statusH = s.statusH)

/*** Insert records directly into local KTA table ***/
WHEN NOT MATCHED THEN
INSERT (statusE, statusF, statusG, statusH, LastModifiedDate, StatusCode, LastModifiedBy, LastReviewedBy, CreatedDate, statusI, statusJ, Email, Mobile, HomePhone, WorkPhone, statusK, statusL, Dob)
VALUES(s.statusE, s.statusF, s.statusG, s.statusH, s.LastModifiedDate, '11', s.LastModifiedBy, s.LastReviewedBy, GETDATE(), s.statusI, s.statusJ, s.Email, s.Mobile, s.HomePhone, s.WorkPhone, s.statusK, s.statusL, s.Dob)

/*** Update records that exist ***/
WHEN MATCHED THEN
UPDATE SET LastModifiedDate = s.LastModifiedDate, LastModifiedBy = s.LastModifiedBy, LastReviewedBy = s.LastReviewedBy, statusI = s.statusI, statusJ = s.statusJ, Email = s.Email, Mobile = s.Mobile, HomePhone = s.HomePhone, WorkPhone = s.WorkPhone, statusK = s.statusK, statusL = s.statusL, Dob = s.Dob;

COMMIT;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK; --rollback transaction of needed
    THROW; --re-raise error to client
END CATCH;
GO