Error Handling in Stored Procedure Try/Catch

Question

I need to add error handling to my stored procedure. I believe it is not usually required to use BEGIN TRAN/COMMIT TRAN when there is just one insert statement. Also what is the significance of using SET XACT_ABORT, NOCOUNT ON statement. Please sugget best/standard way to add the error handling to below SP. I also need to call dbo.usp_get_error_info in the catch segment if it errors . Please suggest.

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

Dan Guzman Dan Guzman · Accepted Answer · 2018-04-17T03:14:10

In a single-statement stored procedure in the default auto commit mode, there is no need to start an explicit transaction or specify SET XACT_ABORT ON. Run-time errors will rollback any changes made by the statement and errors will be returned to the client without additional code.

In multi-statement procs (like the one with EXEC and MERGE in your question), an explicit transaction will ensure all-or-none behavior, allowing you to commit the transaction upon success or rollback if an error occurs. Adding structured error handling ensures the code in the TRY block doesn't continue after an error and the CATCH block provides a convenient place to centralize error handling, typically rolling back the transaction if needed and re-raising the error.

SET NOCOUNT ON suppresses DONE_IN_PROC (rowcount) messages from being returned to a client that doesn't need or expect them. This is especially important with some APIs like ADO classic (not ADO.NET) that require additional programming to handle those additional results.

SET XACT_ABORT ON ensures the transaction is rolled back after an error or client timeout occurs. When a client timeout occurs, the client API sends a cancel request to the stop the executing query so no subsequent code, including the CATCH block, is executed when SQL Server cancels the batch. SET XACT_ABORT ON will rollback the transaction immediately in this case.

Below is a structured error handling example. I didn't include calling dbo.usp_get_error_info in the catch block here because I don't know what it does. THROW will re-raise the original error.

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

Error Handling in Stored Procedure Try/Catch

1 Answers