How to handle exception in sql server stored procedure?

InfoPath alternatives for form designing SharePoint
In this post we will discuss how to use try catch block in SQL Server stored procedure. Also you can check out my previous posts on:
Below is the try catch syntax:
BEGIN TRY
     SQL Statement;
END TRY
BEGIN CATCH
    SQL Statement;
END CATCH
In the catch block you can retrieve error information by using built in functions:
– ERROR_NUMBER() returns the number of the error.
– ERROR_SEVERITY() returns the severity.
– ERROR_STATE() returns the error state number.
– ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
– ERROR_LINE() returns the line number inside the routine that caused the error.
– ERROR_MESSAGE() returns the complete text of the error message.
Example:
Alter PROCEDURE TestStoreProc
AS
GO
BEGIN TRY
     SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage
        ,ERROR_LINE() As LineNumber;
END CATCH;
If you will run the above stored procedure then you will see error message like below:

Similar SharePoint 2013 Tutorials


About Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SharePointSky.com

View all posts by Bijay Kumar →

Leave a Reply