none
Using Try and Catch for Error Handling

    Question

  • I am thinking of using Try and Catch for error handling within my .Net Applications (C#) and also Java, currently if exceptions occur within my code, the application displays the sql server error etc and I can then troubleshoot accordingly, I would like to know whether using Try .. Catch will suppress the error messages from SQL server, If I still want to display error messages within my application as per the current behaviour, how can I maintain same and also use Try and Catch for error handling.

    Also, is it possible to store Try and Catch error handling information in a table and also have the application display it as well as if try and catch wasn’t used to suppress the error information. In effect, I want my application to behave like it currently does i.e display all exceptions within the application itself and I also want to use Try and Catch, but I want to make sure that using Try and Catch wouldn’t change the way my application displays errors.
    Wednesday, January 06, 2010 9:46 AM

Answers

  • Checkout the following T-SQL script implementing error tracking with TRY-CATCH and returning the same error message as currently received with RAISERROR.  Let us know if helpful.

    -- T-SQL TRY-CATCH error catching with logging to table and returning same message
    USE tempdb; 
    GO 
    SELECT 1/0  -- testing error message
    GO
    /*
    Msg 8134, Level 16, State 1, Line 1
    Divide by zero error encountered.
    */
    CREATE TABLE ErrorTracking ( 
      UserName      SYSNAME, 
      SprocName     SYSNAME, 
      ErrorNumber   SYSNAME, 
      ErrorSeverity SYSNAME, 
      ErrorState    SYSNAME, 
      ErrorMessage  NVARCHAR(1024), 
      ModifiedDate  DATETIME    DEFAULT (getdate())); 
    
    GO 
    
    CREATE PROC sprocCalculateAverageSalesPrice( 
               @Qty  int, 
               @SalesAmount    MONEY,
               @AvgSalesPrice MONEY OUTPUT) 
    AS 
       
      BEGIN TRY 
      SET @AvgSalesPrice = @SalesAmount / @Qty
      END TRY 
      BEGIN CATCH 
         INSERT dbo.ErrorTracking 
              (UserName, 
               SprocName, 
               ErrorNumber, 
               errorSeverity, 
               errorState, 
               ErrorMessage) 
        VALUES(suser_sname(),'sprocCalculateAverageSalesPrice',ERROR_NUMBER(),
               ERROR_SEVERITY(),ERROR_STATE(),ERROR_MESSAGE()) 
        DECLARE @SQL nvarchar(max)
        SET @SQL='RAISERROR ('+CHAR(39)+ERROR_MESSAGE()+CHAR(39)+',16,1,1)'
        EXEC sp_executeSQL @SQL 
      END CATCH 
    GO 
    
    -- Test stored procedure 
    -- Execute stored procedure 
    
    DECLARE @AvgPrice money 
    EXEC sprocCalculateAverageSalesPrice  5, 1000, @AvgPrice output
    EXEC sprocCalculateAverageSalesPrice  21, 5000, @AvgPrice output
    EXEC sprocCalculateAverageSalesPrice  0, 2000, @AvgPrice output
    
    /* Messages 
    Msg 50000, Level 16, State 1, Line 1
    Divide by zero error encountered.
    */ 
    SELECT * FROM   ErrorTracking 
    GO
    /*
    UserName	SprocName	ErrorNumber	ErrorSeverity	ErrorState	ErrorMessage	ModifiedDate
    CORPRORATE\Mary Smith	sprocCalculateAverageSalesPrice	8134	16	1	Divide by zero error encountered.	2010-01-08 07:16:47.360 
    */
    DROP PROC sprocCalculateAverageSalesPrice
    DROP TABLE ErrorTracking

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Tuesday, January 12, 2010 8:50 AM
    Friday, January 08, 2010 12:26 PM
    Moderator

All replies

  • If you start catching errors in T-SQL, then the application will not "see" those errors the same way it is seeing it now.

    The following link has an example script for storing caught errors in a logging table:
     
    Using TRY...CATCH in Transact-SQL

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, January 06, 2010 9:54 AM
    Moderator

  • Yes it is possible to store Exception information caught by the CATCH block into a table and also you can add the TRY .. CATCH block to your stored procedures and RAISE the same exception back to the client and make sure you application works as it is before.

    I assume you are not doing anything with the SQL Server error states/ Line numbers etc in the Application Code.

    Here is pseudo code to do it

    BEGIN TRY

      -- code

    END TRY

    BEGIN CATCH                            

      -- build error

      SELECT @errorstate = ERROR_STATE()

            ,@errorSeverity = ERROR_SEVERITY()

      

      -- you can use the same message or build custom message for users     

      SELECT @msg = 'Error - '+ERROR_MESSAGE()+ ', Line  '+convert(varchar,ERROR_LINE())+

                    ', Error Number: '+convert(varchar,ERROR_NUMBER())+

                    ', Error Serverity: '+convert(varchar,ERROR_SEVERITY())

     

      -- insert the error information into table here

     

      -- raise the same error for your client application

      RAISERROR(@msg,@errorseverity,@errorstate) 

     

    END CATCH


    Wednesday, January 06, 2010 11:38 AM

  • Yes it is possible to store Exception information caught by the CATCH block into a table and also you can add the TRY .. CATCH block to your stored procedures and RAISE the same exception back to the client and make sure you application works as it is before.

     

    That would defeat the purpose of doing TRY-CATCH on the server.

    The TRY-CATCH feature can take care of errors without the application "knowing" about it, and continue processing.

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Wednesday, January 06, 2010 12:20 PM
    Moderator
  • In Catch block we can return our own exception messages in addition to logging the error.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 06, 2010 2:53 PM
    Moderator
  • Most business applications do require that errors are both logged and raised to the client layer. When error occurs at the database layer you cannot silently log it and continue processing. An error indicates something is wrong and processing did not finish successfully, so the error needs to be raised to the client the presented with user friendly notification. Think of the process of generating billing statements. If there is an error you cannot just log it and forget about it, the billing statements will never be generated if the user does not take appropriate action to correct the problem and restart the calculation.


    Plamen Ratchev
    Wednesday, January 06, 2010 4:32 PM
    Moderator
  • HI SQL USA,

    Fully agree with you on handling errors with out let client application know about it. 

    As the requester specifically asked that his application should not see any change in the way it handles specific errors. I told to RAISE the same error after taking the necessary action of logging into the table or rolling back any transacction.



    Wednesday, January 06, 2010 5:12 PM
  • still dont fully understand, if i want to log errors and also make the application work as it currently works, how do i do it. I dont want the application silently working as if nothing happened.
    Friday, January 08, 2010 11:54 AM
  • Checkout the following T-SQL script implementing error tracking with TRY-CATCH and returning the same error message as currently received with RAISERROR.  Let us know if helpful.

    -- T-SQL TRY-CATCH error catching with logging to table and returning same message
    USE tempdb; 
    GO 
    SELECT 1/0  -- testing error message
    GO
    /*
    Msg 8134, Level 16, State 1, Line 1
    Divide by zero error encountered.
    */
    CREATE TABLE ErrorTracking ( 
      UserName      SYSNAME, 
      SprocName     SYSNAME, 
      ErrorNumber   SYSNAME, 
      ErrorSeverity SYSNAME, 
      ErrorState    SYSNAME, 
      ErrorMessage  NVARCHAR(1024), 
      ModifiedDate  DATETIME    DEFAULT (getdate())); 
    
    GO 
    
    CREATE PROC sprocCalculateAverageSalesPrice( 
               @Qty  int, 
               @SalesAmount    MONEY,
               @AvgSalesPrice MONEY OUTPUT) 
    AS 
       
      BEGIN TRY 
      SET @AvgSalesPrice = @SalesAmount / @Qty
      END TRY 
      BEGIN CATCH 
         INSERT dbo.ErrorTracking 
              (UserName, 
               SprocName, 
               ErrorNumber, 
               errorSeverity, 
               errorState, 
               ErrorMessage) 
        VALUES(suser_sname(),'sprocCalculateAverageSalesPrice',ERROR_NUMBER(),
               ERROR_SEVERITY(),ERROR_STATE(),ERROR_MESSAGE()) 
        DECLARE @SQL nvarchar(max)
        SET @SQL='RAISERROR ('+CHAR(39)+ERROR_MESSAGE()+CHAR(39)+',16,1,1)'
        EXEC sp_executeSQL @SQL 
      END CATCH 
    GO 
    
    -- Test stored procedure 
    -- Execute stored procedure 
    
    DECLARE @AvgPrice money 
    EXEC sprocCalculateAverageSalesPrice  5, 1000, @AvgPrice output
    EXEC sprocCalculateAverageSalesPrice  21, 5000, @AvgPrice output
    EXEC sprocCalculateAverageSalesPrice  0, 2000, @AvgPrice output
    
    /* Messages 
    Msg 50000, Level 16, State 1, Line 1
    Divide by zero error encountered.
    */ 
    SELECT * FROM   ErrorTracking 
    GO
    /*
    UserName	SprocName	ErrorNumber	ErrorSeverity	ErrorState	ErrorMessage	ModifiedDate
    CORPRORATE\Mary Smith	sprocCalculateAverageSalesPrice	8134	16	1	Divide by zero error encountered.	2010-01-08 07:16:47.360 
    */
    DROP PROC sprocCalculateAverageSalesPrice
    DROP TABLE ErrorTracking

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Tuesday, January 12, 2010 8:50 AM
    Friday, January 08, 2010 12:26 PM
    Moderator
  • Thanks very much for this, I will try this out on a test platform.
    Tuesday, January 12, 2010 9:33 AM