Exception Handling in SQL Server Stored Procedure with TRY CATCH

Introduction:

Here I will explain how to handle exceptions in SQL Server or exception handling in SQL Server for stored procedure by using try catch or error handling in SQL Server.

Description:
 
In previous articles I explained Pass table as parameter to stored procedure in SQL Server, Difference between joins in SQL Server, Convert rows to columns in SQL Server, SQL Query to get duplicate records count and many articles relating to SQL Server, jQuery, JavaScript. Now I will explain how to handle exceptions in SQL Server.

To handle exceptions in SQL Server we can use TRY…… CATCH blocks. To use TRY…… CATCH blocks in stored procedure we need to write the query like as shown below




BEGIN TRY
---Write Your Code
END TRY
BEGIN CATCH
---Write Code to handle errors
END CATCH
In TRY block we will write our queries and in CATCH block we will write code to handle exceptions. In our SQL statements if any error occurs automatically it will move to CATCH block in that we can handle error messages. To handle error messages we have defined Error Functions in CATCH block those are

ERROR_LINE() - This function will return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - This function will return error number which is unique and assigned to it.

ERROR_SEVERITY() - This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - This function will return state number of error message which cause to raise error.

ERROR_PROCEDURE() - This function will return name of the procedure where an error occurred.

ERROR_MESSAGE() - This function will return the complete text of the error message which cause to raise error.

Check below sample query to handle errors in stored procedure


BEGIN TRY
SELECT 300/0
END TRY
BEGIN CATCH
SELECT ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE(),
ErrorProcedure = ERROR_PROCEDURE(), ErrorLine = ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()
END CATCH
If we run above query we will get output like as shown below

Output