Exception Handaling in SQL Server Stored Procedure with Try Catch

I will explain how to handle Exception in SQl Server for Stored Procedure by using Try Catch.


To handle Exception in SQL Server we can use Try Catch block.To use Try Catch we need to write the code as shown bellow.

BEGIN TRY

---Write Your Code

END TRY

BEGIN CATCH

---Write Code to handle errors

END CATCH

In Try block we write our queries and in Catch block we write a code to handle exceptions. In SQL statements if any error occurs automatically it will go to Catch block where 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.

It will show output like this..