March 05, 2010

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:

ErrorHandling StoredProcedure


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "Add Error Handling to an Existing Stored Procedure"
  1. Claudia Lawrence said...
    March 8, 2010 at 4:51 AM

    Hi, ur blog is really nice & informative, while reading it I truly like it. I just wanna suggest that u should submit your blog in this website which is offering very unique features at cheap prices there are expert advertising team who will not only provide the adspace but also promote ur blog & affiliate ads through all over the networks which will definitely boost ur traffic & readers. Finally I have bookmarked ur blog & also shared to my friends. hope u have a wonderful day & !!happy blogging!!.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions