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:
1 comment:
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!!.
Post a Comment