Add Error Handling to an Existing Stored Procedure

A lot of developers find Error Handling to be a tedious task, and in some cases it is. As a result, a lot of them skip error handling assuming the code would run just fine. However in a practical scenario, assuming that the code works fine is a myth.

Here’s a technique of how to add Error Handling to an Existing Stored Procedure without changing the code of the stored procedure. This technique was shared by a colleague of mine called Henry Perkins and I am thankful to him for the same. So let us say there is a stored procedure called uspSampleProc. To add error handling to this stored proc, use the following code. I am using the Northwind database to create this stored procedure with an exception

USE Northwind
GO
CREATE PROCEDURE uspSampleProc
AS
INSERT INTO Customers(CustomerID, CompanyName)
VALUES('ALFKI','TEST');
GO
BEGIN TRY
    EXEC uspSampleProc
END TRY
-- Catch the error
BEGIN CATCH
    SELECT ERROR_Number() as ErrorNumber,
    ERROR_MESSAGE() as ErrorMessage,
    ERROR_PROCEDURE() as InStoredProcedure,
    ERROR_STATE() as ErrorState,
    ERROR_SEVERITY() as ErrorSeverity,
    ERROR_LINE() as ErrorLine
END CATCH

Since ALFKI already exists in the Customer table, there is a violation of primary key constraint which is caught by our Error Handling code. The output of running this code is as follows: