SQL TRY CATCH

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

For example, we write a series of statements inside the TRY block. If the SQL Server finds an error, then 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.

The following are the list of things to remember before you start working with the SQL Try catch

  • 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 the 10, and lower than 20.
  • SQL allows you to use the Nested TRY blocks (TRY CATCH inside another).
  • However, 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
	-- SQL Statements;
END TRY
BEGIN CATCH
        --SQL Statements;
END CATCH

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

SQL TRY CATCH Example 1

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

-- Example for SQL Server TRY CATCH Block
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
SQL TRY CATCH 1

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 what 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 SQL Try catch example, we show how the try catch will actually work. Here, we are using the different print statements to display the start and END of both SQL TRY and Catch block.

-- SQL Server TRY CATCH Example
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

SQL TRY CATCH 3

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

SQL TRY CATCH 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

-- SQL Server TRY CATCH Example
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 example, first, we declared two tinyint variables

DECLARE @Number TINYINT,
	@Result TINYINT

Next, within the TRY block, we assigned 255 to Number variable, and performing 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'
SQL TRY CATCH 4

If you observe the above Try catch 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; control will exit from the TRY block, and immediately enters into Catch block.