AppDividend
Latest Code Tutorials

SQL Try Catch Example | Implement Error Handling in SQL

0

Sql Try Catch Example | Implement Error Handling in SQL is today’s topic. TRY CATCH construct is used for Exception handling. Like C++ Exception Handling, Java Exception Handling, Python Try Except Example is also used for handling errors effectively in SQL. For example, when we write series of statements inside the TRY block and when SQL Server finds any error then it will exit from the TRY block, and enter inside the CATCH block, and will execute the statements inside the CATCH block.

Sql Try Catch

One should remember the following list of things before starting SQL TRY CATCH Construct.

  1. The CATCH block must follow every TRY block. One is not allowed to include any statements between the END TRY, and BEGIN CATCH
  2. The controller will execute statements after the END CATCH. If there are no errors in the TRY block. This will happen because control will not enter the Catch block.
  3. If there is an error in a TRY block, then the control will immediately exit from the Try block and enters into the Catch block.
  4. SQL allows us to use the Nested TRY blocks.
  5. Compile errors and Syntax errors are not handled by the SQL TRY CATCH Construct.

See the following syntax.

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

Following are the list of functions which can be used inside the SQL CATCH block to obtain the information of errors, they are the following.

  1. ERROR_MESSAGE (): It is used for returning the complete description of an error. For example, Arithmetic Overflow.
  2. ERROR_LINE (): It returns on which line the error has occurred.
  3. ERROR_NUMBER (): It is used for returning the Error number.
  4. ERROR_SEVERITY (): It is used for displaying the severity of the error.
  5. ERROR_PROCEDURE(): It returns the name of a Trigger or Stored Procedure in which an error has occurred.
  6. ERROR_STATE (): It is used for returning the state number of an error.

Let’s understand this with the help of following proper examples.

See the following code.

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

See the following output.

Error Message ErrorLine Error Number Error Severity Error State
Divide by zero error encountered. 2 8134 16 1

 

#EXPLANATION

SELECT 10/0 AS Result was the statement due to which error occurred because we know that any number divided by zero will result in an error and as this statement was inside the try block, an error occurred, and control jumped to catch block, and all the statements present within that block was executed.

#EXAMPLE 2:

This example will show how the try-catch block statement works.

DECLARE @Number TINYINT,
	       @Result TINYINT
BEGIN TRY
    PRINT N' This Message is From Start of the TRY BLOCK'
    SET @Number = 220;
    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

See the following output.

This Message is From Start of the TRY BLOCK
Value Stored in @Number Variable = 220
This Message is From End of the TRY BLOCK

#EXAMPLE 3:

MODIFICATION OF the PREVIOUS 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

See the following output.

This Message is From Start of the TRY BLOCK
Value Stored in @Number Variable = 255
This Message is From Start of the CATCH BLOCK
Error Message = Arithmetic overflow error for data type tinyint, value=256.
Error Number = 220
Error Line = 7
This Message is From End of the CATCH BLOCK

#EXPLANATION

SET @Result = @Number + 1; this was the statement due to which error was occurred.

This happened because previously we declared value 255 to @Number variable and as we know that tinyint can hold the maximum value of 255 and when the value exceeded, i.e. 256 it’s overflowed which resulted in switching the control from TRY block to CATCH block.

Finally, SQL Try Catch Example | Implement Error Handling in SQL is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.