This procedure involves the use of two different blocks named TRY and CATCH. Write the query or statement (which you think might cause an error) inside the TRY block. If an exception occurs during the execution of the statement inside the TRY block, it will immediately be send to the CATCH block where the error will be handled and shown a message. You can write a procedure inside the CATCH block, which will log the error.
BEGIN TRY SQL Statement END TRY BEGIN CATCH Error Messages, SQL Statement END CATCH
You must handled Errors or Exceptions with utmost care, as these are inevitable in many ways. A single error in the software or hardware can bring down a Rocket before or after the launch. Exception handling however, is no Rocket science and with a little practice, you can master it and make use of it in your databases.
So now, you know how important it is to handle errors. Unhandled errors can wreak havoc and can give sleepless nights to DBA’s and database programmer. Errors can also give us as an insight of a situation and so it is always advisable to log errors, if at all they ever occur during a process.
Here's an example showing how to execute exception handling. We will insert a row of data in our Books table, which I have previously created. To insert the data we will first create a stored procedure, which will also have the Try…Catch block to handle the errors. We will not pass any parameters to the procedure and rather use fixed values to prove our point.
CREATE PROCEDURE Exception_Demo AS BEGIN TRY INSERT INTO dbo.Books (BookName, Category, Price, Price_Range) SELECT 'Learning OpenCV', 'Computers', 'Two Hundred', '200-500' END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH
Executing this procedure will throw an error, and is caught in the Catch block.
The books table will accept four values (if you have checked the books table) and as you know that, the price column will accept only numeric values. We have intentionally passed a varchar value (Two Hundred), not accepted by the table, and will throw an error.
The error message shown in the above example is a system-generated message. Moreover, this message is displayed after collecting information about the error using a system function called the ERROR_MESSAGE().
SQL Server has six predefined system functions used in the Catch block.
1) ERROR_MESSAGE() – This function will return a text message explaining the error. We can embed or add our own message along with the predefined message.
BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'AN ERROR OCCURED: ' + ERROR_MESSAGE() AS Custom_Error END CATCH
The result is “AN ERROR OCCURED: Divide by zero error encountered.”
We have added three more words in the beginning of the actually error.
2) ERROR_NUMBER() - Returns the number of the error. Normally these numbers work as conditions in the Catch block, which helps writing custom messages. Every SQL Server error has uniquely defined error messages along numbers.
Note: To see all the system errors, run the below query.
3) ERROR_SEVERITY() - Returns the severity of the error. While executing the above query you might have noticed the second column named severity. This indicates the nature of the error.
SELECT DISTINCT severity FROM master.dbo.sysmessages ORDER BY severity
Now let us again run the procedure, which we have written in the first function above. However, we will add and remove couples lines to understand it better.
BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR(2)) AS Error END CATCH
The result is ERROR SEVERITY: 16.
Severity level 16 indicates that the error has occurred due to a user problem and it can be fixed by the user.
MSDN has the entire list of “Severity” levels defined.
4) ERROR_STATE() This function will return an Integer value that indicates the exact location in the code where the error has occured. In a multi user environment, the “state” of the error can help locate and diagnose errors with ease.
SELECT ERROR_STATE() AS ErrorState
5) ERROR_PROCEDURE() Returns the name of the procedure that threw the error. You can experiment this function with the procedure (Exception_Demo) we have written before. Just add the following line inside the catch block.
SELECT ERROR_PROCEDURE() AS Stored_Procedure
If it is not a procedure, but a query then it will return as NULL.
6) ERROR_LINE() Returns the line number where the actual error has occurred. This helps developers to pinpoint the place or error (code) and fix it.
The actual purpose of exception handling using these blocks is to show users a more readable error messages also prevent the entire process from crashing.
We can write a nested TRY_CATCH block, which means we can write TRY_CATCH block inside another TRY_CATCH block. The Catch block can have SQL statements (queries) as well.