SQL TRY CATCH

The SQL Try Catch helps you handle query errors effectively. Like exception handling in Java or C#, SQL Server provides us with the TRY CATCH construct.

For example, we write a series of statements inside the TRY block. Then, if the SQL Server finds an error, it exits from the TRY block and enters into the CATCH block; it executes the statements inside the CATCH block. And lastly, it will return the corresponding error description.

Before you start working with the SQL Try catch, you have to remember the following list of things.

The CATCH block must immediately follow every TRY block. You are not allowed to include any statements between the END TRY and BEGIN CATCH.
If there are no errors in a TRY block, the control will not enter into the CATCH block. It means the controller will execute statements after the END CATCH.
If there is an error in the TRY block, it immediately exits from the Try block and enters into the Catch block.
The SQL TRY CATCH will catch all the errors whose severity is higher than 10 and lower than 20.
It allows you to use the Nested TRY blocks (TRY CATCH inside another).
However, the CATCH block can not handle the compile errors like Syntax errors.

SQL TRY CATCH Syntax

The syntax of the SQL Server Try Catch is

BEGIN TRY
	-- Statements;
END TRY
BEGIN CATCH
        -- Statements;
END CATCH

Within the Catch block, use the following system function to obtain the information about an error.

ERROR_MESSAGE(): It returns the complete description of a mistake. For example, Arithmetic Overflow, etc.
ERROR_LINE(): It returns on which line an error occurs.
ERROR_NUMBER(): It returns the Error number.
ERROR_SEVERITY(): It displays the severity of an error.
ERROR_PROCEDURE(): It returns the name of the Trigger or Stored Procedure on which the error occurs.
ERROR_STATE(): It returns the state number of an SQL Server error.

SQL TRY CATCH Example

In this Try Catch example, we will find the result of 10/0.

BEGIN TRY
	SELECT 10/0 AS Result;
END TRY
BEGIN CATCH
	SELECT ERROR_MESSAGE() AS [Error Message]
	      ,ERROR_LINE() AS ErrorLine
	      ,ERROR_NUMBER() AS [Error Number]  
              ,ERROR_SEVERITY() AS [Error Severity]  
              ,ERROR_STATE() AS [Error State]  
END CATCH

Within the SQL TRY block, we are finding the result of 10/0. As we all know, anything divided by zero is an error. Once it encounters an error, it will enter the CATCH block.

SELECT 10/0 AS Result;

This SQL try catch query will return the State, Severity, error number, at which line error has occurred, and the message that explains an error.

SELECT ERROR_MESSAGE() AS [Error Message]
      ,ERROR_LINE() AS ErrorLine
      ,ERROR_NUMBER() AS [Error Number]  
      ,ERROR_SEVERITY() AS [Error Severity]  
      ,ERROR_STATE() AS [Error State]

TRY CATCH Example 2

In this example, we show how the process will actually work. Here, we are using the different print statements to display the start and END of both SQL TRY and Catch blocks.

DECLARE @Number TINYINT,
	@Result TINYINT
BEGIN TRY
    PRINT N'This Message is From Start of the TRY BLOCK'
    SET @Number = 254;
    PRINT N'Value Stored in @Number Variable = ' + CAST(@Number AS VARCHAR)
    SET @Result = @Number + 1;
    PRINT N'This Message is From End of the TRY BLOCK'
SELECT @Number AS Number, 
       @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'This Message is From Start of the CATCH BLOCK'
    PRINT N'Error Message = ' + ERROR_MESSAGE()
    PRINT N'Error Number = ' + CAST(ERROR_NUMBER() AS VARCHAR)
    PRINT N'Error Line = ' + CAST(ERROR_LINE() AS VARCHAR)
    PRINT N'This Message is From End of the CATCH BLOCK'
END CATCH

Results Tab

Number | Result
---------------
254    | 255

Let me check the Message tab

From the above screenshot, see that it only prints the statements from the SQL TRY block.

SQL TRY CATCH Error Handling Example 3

How to use the try catch to handle the errors. It is the same code as the previous example, but we changed the @Number from 254 to 255

DECLARE @Number TINYINT,
	@Result TINYINT
BEGIN TRY
    PRINT N'This Message is From Start of the TRY BLOCK'
    SET @Number = 255;
    PRINT N'Value Stored in @Number Variable = ' + CAST(@Number AS VARCHAR)
    SET @Result = @Number + 1;
    PRINT N'This Message is From End of the TRY BLOCK'
SELECT @Number AS Number, 
       @Result AS Result;
END TRY
BEGIN CATCH
    PRINT N'This Message is From Start of the CATCH BLOCK'
    PRINT N'Error Message = ' + ERROR_MESSAGE()
    PRINT N'Error Number = ' + CAST(ERROR_NUMBER() AS VARCHAR)
    PRINT N'Error Line = ' + CAST(ERROR_LINE() AS VARCHAR)
    PRINT N'This Message is From End of the CATCH BLOCK'
END CATCH

ANALYSIS

Within this SQL try catch error handling example, First, we declared two tinyint variables.

DECLARE @Number TINYINT,
	@Result TINYINT

Next, within the TRY block, we assigned 255 to the Number variable and performed addition for the result variable.

PRINT N'This Message is From Start of the TRY BLOCK'
    SET @Number = 255;
    PRINT N'Value Stored in @Number Variable = ' + CAST(@Number AS VARCHAR)
    SET @Result = @Number + 1;
    PRINT N'This Message is From End of the TRY BLOCK'

As we all know, that tiny int holds up to 255 means Overflow. So, it will exit from the TRY block and execute the statement inside our CATCH block, which is:

    PRINT N'This Message is From Start of the CATCH BLOCK'
    PRINT N'Error Message = ' + ERROR_MESSAGE()
    PRINT N'Error Number = ' + CAST(ERROR_NUMBER() AS VARCHAR)
    PRINT N'Error Line = ' + CAST(ERROR_LINE() AS VARCHAR)
    PRINT N'This Message is From End of the CATCH BLOCK'

If you observe the above example screenshot, though we have the print statement at the end of the TRY block, the controller skipped that statement. It is because once it enters into SET @Result = @Number + 1; the control will exit from the TRY block and immediately enters into the Catch block.