Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Exception handling in SQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 781
    Comment on it

    Exception handling in SQL

    Whenever an exception occurs our code gets disrupted. Exception handling is a way to handle these disruptions,  Like we may log when an exception occurs or we may raise error when any exception occurs. We can use TRY and CATCH to handle exceptions.

     

    Syntax

    BEGIN TRY  
        SQL statements
    END TRY  
    BEGIN CATCH  
        Logging errors
    END CATCH;  

     

    A simple example of an exception

    BEGIN TRY  
        SELECT 1/0;
    END TRY  
    BEGIN CATCH  
         SELECT  
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
    END CATCH; 

     

    Output after executing above query

    1) ERROR_NUMBER() returns the number of the error.
    2) ERROR_SEVERITY() returns the severity of error.
    3) ERROR_STATE() returns the error state of number.
    4) ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
    5) ERROR_LINE() returns the line number at which error occurred.
    6) ERROR_MESSAGE() returns the text message of error.

    You can log these in a table as well.

     

    Errors that are not affected by TRY CATCH

    Here, I am trying to get all info from non existing table Employees. Here, error will not be catch.

    BEGIN TRY  
        SELECT * FROM Employees 
    END TRY  
    BEGIN CATCH  
        SELECT  
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
    END CATCH;  

     

    Output after executing above query

     

    If this error occurred inside stored procedure then it will be caught by CATCH block

    Suppose "SELECT * FROM Employees" is inside SP and we call SP from try block then error will be caught at catch block.

     

    Example

    Assuming employees table doesn't exists. Execute following SP.

    Now, call this SP within try block.

    BEGIN TRY  
        EXECUTE GetEmployees 
    END TRY  
    BEGIN CATCH  
        SELECT  
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
    END CATCH;  

     

    Output after executing above script

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: