Try… Catch Exception Handling in SQL Server

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.

Syntax

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.

For 2014 – MSDN Database Engine Error Severities
For 2000 and Above – MSDN Error Message Severity Levels

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.

← PreviousNext →