Exception Handling In SQL Server

If you are aware of any programming language. Then you have to know about the exception handling like “Try, Catch” blocks .This is used in Sql Server as well.

Firstly,

Sql Server 2005 introduced Try Catch blocks to handle errors effectively from the backend side.

The handling of the exception can provide more and specific information of errors and translate those errors into some meaningful message to make the user understand by logging and saving to the table.

Now, we will understand in depth how it works.

Syntax:

BEGIN TRY
//Your Logic
END TRY
BEGIN CATCH
//if any exception came in try block you can handle here
END CATCH

Your logic should come inside the BEGIN TRY and END TRY . If during any transaction in any line inside the try block exception is raised, then it will send to CATCH block .

Inside the Catch block you will have that exception. You can do anything with that exception like you can log the error or report that error or you can throw that exception to the front end user with any meaningful statement. In this way we can try to fix raised exceptions.

We can use some special in-built functions inside catch block, Let's have a look at it :

ERROR_NUMBER() - returns the internal number of the error.

ERROR_STATE() - returns the information about the source.

ERROR_SEVERITY() - returns the severity of error occurred. We can call anywhere but returns severity if it is in the catch block else returns NULL.

ERROR_PROCEDURE() - returns the Name of the Procedure.

ERROR_LINE() - returns the line number where error occured.

ERROR_MESSAGE() - returns the exact error message.

This information is very useful when you are doing big projects like banking, Ticket booking App etc. When we are working on such projects, there comes the COMMIT and ROLLBACK keywords into the picture.

For eg: We have to write COMMIT in try block and ROLLBACK into Catch block, Suppose a transaction is going on and in middle some error occured then it will go to Catch block and ROLLBACK the whole Transaction and save the error related log,If transaction is complete we can COMMIT.