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 an error message like below:

You May Also like the Following SharePoint Online 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