| Handling errors by use of TRY and CATCH in SQL?
Below is the syntax to use of TRY and CATCH block. In below example we have two blocks of code:-
Anything we write in BEGIN TRY and END Try is the code which we want to monitor for any error. So if an error happend inside of TRY statement, control will immediately get transferred to the CATCH statement and then execute the code line by line of statement in between CATCH block.
Inside the CATCH statement we can fix the error, or we can report the error or evn log error in DB Table. Here in logs we can fectch the username and all useful stuff. And we can even have access to some special data only available inside the CATCH statement:-
- Error_Number:-This will return the internal number of error.
- ERROR_STATE:-This will return the information about the source
- ERROR_SEVERITY:-This will return the information about anything from informational errors to errors user of DBA can fix, etc.
- ERROR_LINE:-This will returns the line number at which an error happened on.
- ERROR_PROCEDURE:-This will return the name of the store procedure or function
- ERROR_MESSAGE:-This will return the most essential information and that is the message text of the error.
When we say about the TRY CATCH block it is quite easy to handle as compare the use of transactions. And the reason of this is you need to take care of COMMIT or ROLLBACK transaction. The problem is if an error occurs after we begin but before we commit or rollback.
And below is the syntax to get the detail of error and create a logs for the error.
Here in above example we have tried for one error in TRY block divide 1 by 0 so that we will get the error and the code jump to CATCH block to capture the error. | | |