In SQL Server-Exception Handling Template for Stored Procedure
This article, in the series of articles on Exception Handling in SQL Server. Below is the complete list of articles in this series.
Exception Handling Template
below is the exception handling template one should be using for proper error handling in SQL Server. Note the THROW statement works only in SQL Server 2012. You can validate its correctness and then use it in your development work.CREATE PROCEDURE dbo.ErrorHandlingTemplate
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON
SET XACT_ABORT ON
-- Code Which Doesn't Require Transaction
BEGIN TRANSACTION
-- Code which Requires Transaction
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
ROLLBACK TRAN
-- Do the Necessary Error logging if required
-- Take Corrective Action if Required
THROW --RETHROW the ERROR
END CATCH
END
Comments