Exception Handling in Sql Server

Next Post
By Admin at 2 Dec 2009, 19:30 PM
  • Starting in SQL Server 2005, the exception handling has been very similar to the exception handling in c++ and c#. SQL Server 2005 introduced the Try and Catch blocks. You can place all your T-SQL statements in the TRY block. If an error occurred in any T-SQL statements within the TRY block, then the control will jump to the nearest CATCH block. In the CATCH block you can do any extra processing (e.g., rollback any remaining transactions) and if you want to re-throw the error, then you can use RAISERROR command. But if there was no error in the TRY block, the CATCH block will be skipped.

    Syntax

    1. BEGIN TRY
    2. { sql_statement | statement_block }
    3. END TRY
    4. BEGIN CATCH
    5. { sql_statement | statement_block }
    6. END CATCH
    7. [ ; ]

    Arguments

    sql_statement is any Transact-SQL statement.
    statement_block set of T-SQL batch statements

    Example

    1. BEGIN TRY
    2. BEGINTRANSACTION
    3. INSERTINTO customers(CName,Phone,Address)
    4. VALUES ('THAJ', 8965569823, 'West Street')
    5. COMMITTRANSACTION
    6. print 'Insert succeeded.'
    7. END TRY
    8. BEGIN CATCH
    9. IF @@TRANCOUNT > 0
    10. ROLLBACKTRANSACTION;
    11. Print 'Insert failed'
    12. END CATCH


    In the TRY block, we included an insert statement. If the insert statement executes correctly, then the message “Insert succeeded.” will be displayed. Otherwise, the “Insert failed” message will be displayed from the Catch block.

    Exception Handling Functions


    In the CATCH block, we can use the following functions to get the information about the current error that caused the CATCH block to run:

    1. ERROR_NUMBER()
    It returns the error number of the current error.

    2. ERROR_MESSAGE()
    It returns the message text of the current error.

    3. ERROR_SEVERITY()
    It returns the severity of the current error.

    4. ERROR_STATE()
    It returns the state number of the current error.

    5. ERROR_LINE()
    It returns the line number at which the current error occurred.

    6.ERROR_PROCEDURE()
    It returns the name of the stored procedure or trigger where the current error occurred.

    Example

    The following example will generate the dive-by-zero error:

    1. BEGIN TRY
    2. -- Generate a divide-by-zero error.
    3. SELECT 1/0;
    4. END TRY
    5. BEGIN CATCH
    6. SELECT ERROR_NUMBER() AS ErrorNumber;
    7. END CATCH;

    Result


    ErrorNumber
    8134

    In the above example, we displayed only the error number. By use the above functions we can display other information about the error as illustrated below:

    1. BEGIN TRY
    2. -- Generate a divide-by-zero error.
    3. SELECT 1/0;
    4. END TRY
    5. BEGIN CATCH
    6. SELECT
    7. ERROR_NUMBER() AS ErrorNumber,
    8. ERROR_SEVERITY() AS ErrorSeverity,
    9. ERROR_STATE() AS ErrorState,
    10. ERROR_PROCEDURE() AS ErrorProcedure,
    11. ERROR_LINE() AS ErrorLine,
    12. ERROR_MESSAGE() AS ErrorMessage;
    13. END CATCH;

    Result

    undefined


    Comments

     

    Post a comment

    Please correct the following: