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:

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook