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

Question

我需要在我的存储过程中添加错误处理。我相信当只有一个insert语句时，通常不需要使用BEGIN TRAN/COMMIT TRAN。还有使用SET XACT_ABORT，NOCOUNT ON语句的意义是什么。请给出最佳/标准的方法将错误处理添加到下面的SP中。如果出现错误，我还需要在catch段中调用dbo.usp_get_error_info。请建议。

USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UspSdtSync]
AS
BEGIN

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

SET                        @RunID = NULL  
SET                        @IntraDayID = NULL

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;

END
GO

Answer 1

在默认自动提交模式下的单语句存储过程中，不需要启动显式事务，也不需要在上指定＜code＞SET XACT_ABORT。运行时错误将回滚该语句所做的任何更改，错误将返回到客户端，而无需额外代码。

在多语句过程中(比如您问题中的< code>EXEC和< code>MERGE)，显式事务将确保全有或全无行为，允许您在成功时提交事务，或者在出现错误时回滚事务。添加结构化错误处理可确保< code>TRY块中的代码在出错后不会继续运行，并且< code>CATCH块提供了一个集中错误处理的便利位置，通常在需要时回滚事务并重新引发错误。

SET NOCOUNT ON禁止将DONE_IN_PROC（rowcount）消息返回给不需要或不期望它们的客户端。这对于ADO经典（而不是ADO.NET）等需要额外编程来处理这些额外结果的某些API尤其重要。

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

下面是一个结构化错误处理的例子。我没有在这里的catch块中包括调用< code > dbo . USP _ get _ error _ info ,因为我不知道它做什么。< code>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