Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : Error handling using TRY-CATCH

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 392
    Comment on it

    Before SQL Server 2005, the only practical way to trap errors in SQL was using the old-fashioned @@error system variable. Although this is still supported, in this blog we will learn how to use TRY-CATCH for handling errors.TRY-CATCH block is similar to the one which we have in C#, but it doesnt have the FINALLY block. If any error occurs in the statements enclosed in the TRY block then the control is immediately passed to the associated CATCH block.

    Below is the structure if the TRY-CATCH block:

    BEGIN TRY
    -- sql statements which are potential source of errors
    END TRY 
    BEGIN CATCH
    -- in case error occurs it will be handled here
    END CATCH
    

    Below is the description of error functions used within CATCH block

    1) ERROR_NUMBER()

    It is the error number and its value is same as returned by @@ERROR function.

    2) ERROR_LINE()

    This provides the line number of the batch or stored procedure where the error occurred.

    3) ERROR_SEVERITY()

    This returns the severity level of the error.

    4) ERROR_STATE()

    This returns the state number of the error.

    5) ERROR_PROCEDURE()

    This returns the name of the stored procedure or trigger where the error occurred.

    6) ERROR_MESSAGE()

    The complete text of the error message.The text includes the values supplied for any substitute parameters, such as lengths, object names, or times.

    Running the below proc will provide details for divide by zero error.

    CREATE PROCEDURE uspTryCatch
    AS
    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
    

    Following are points worth noting regarding TRY-CATCH usage:

    1) TRY..CATCH block combination catches errors whose severity lies between 11 and 19.

    2) CATCH block is executed only if there is an error in T-SQL statements within TRY block.

    3) Each CATCH block is associated with only one TRY block and vice versa.

    4) Inside the TRY..CATCH block XACT_STATE function can be used to check whether an open transaction is committed or not. It will return -1 if transaction is not committed else it will return 1.

 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: