none
Try Catch block is not work that try block failed but catch block is not excuted

    Question

  • I am facing the problem: Try Catch block is not work that try block failed but catch block is not excuted.

    In my case, I tried to excuted the sql like below:

    begin try

    update [table] set [column] = abc

    end try

    begin catch
     select 'catch error'
    end catch

     

    The [column] is numeric format, but data set to it is not valid number.

    When excute this sql, the following error prompt out:

    Invalid column name 'abc'.

    And the catch block has not been excuted.

     

    I want to know what happen in this case.

    Moreover, I found an article talks about the similar case too:

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/13/your-try-block-may-fail-and-your-catch-block-may-be-bypassed.aspx

     

    Is there any solution so that I can catch the error without stopping the batch?

    Thanks a lot.

    Monday, August 02, 2010 2:14 AM

Answers

  • There is some information about when a CATCH block is ignored, look for the section that says "Errors Unaffected by a TRY...CATCH Construct".

     

    http://msdn.microsoft.com/en-us/library/ms175976.aspx

     

    From that link:

    The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
    
    Compile errors, such as syntax errors, that prevent a batch from running.
    
    Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
    

     

    There is something wrong with the syntax of your update statement.  If you can include the specific syntax of the update statement, it would be easier to figure out why you are getting that error.

     

    Monday, August 02, 2010 2:42 AM
  • > steven043 had the answer to my problem which was updating a table or column that doesn't exist.  Apparently the compiler checks this regardless if you are checking it, calls it an error without ever executing it.  Apparently Microsoft tried to be smart for the developer on this one and I think it back-fired.

    Well, if you in C# try to access a member that does not exist, do you expect the code to compile and then blow up at run-time? (Alternatively transfer control to your catch handler.)

    What is a little strange in SQL Server is that due to deferred name resolution, compilation errors can happen at run-time, after the TRY block has been entered, and they are still not caught in the local CATCH handler.

    Note that with column names this is less likely to happen, since there is no deferred name resolution on columns. It would only happen if a column is dropped after the initial compilation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, September 29, 2010 10:19 PM

All replies

  • There is some information about when a CATCH block is ignored, look for the section that says "Errors Unaffected by a TRY...CATCH Construct".

     

    http://msdn.microsoft.com/en-us/library/ms175976.aspx

     

    From that link:

    The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
    
    Compile errors, such as syntax errors, that prevent a batch from running.
    
    Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
    

     

    There is something wrong with the syntax of your update statement.  If you can include the specific syntax of the update statement, it would be easier to figure out why you are getting that error.

     

    Monday, August 02, 2010 2:42 AM
  • any solution so that I can catch the error without stopping the batch?


    You can give nesting try-catch a try. Let us know if works.

    Article on nesting try-catch: http://www.sqlusa.com/articles2008/trycatch/

     


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Monday, August 02, 2010 5:21 AM
    Moderator
  • Though Try..Catch block does not catch few errors or ignored at some places. It should work in your case.

    Error describe itself, that you are updating column with another column abc that does not exists in the table... actually you need to pass abc as literal value (wrap it in single quote). check out below code snippet.

    CREATE TABLE #MyTable (NumericCol INT)
    
    --TEST DATA
    INSERT INTO #MyTable
    VALUES(10)
    
    --TEST DATA VERIFICATION
    SELECT * FROM #MyTable
    
    BEGIN TRY
    	UPDATE #MyTable
    	SET NumericCol = 'ABC' -- STRING VALUE
    END TRY
    BEGIN CATCH
    	SELECT 'ERROR COUGHT' AS ACTION, ERROR_NUMBER() AS ERRNo, ERROR_MESSAGE() AS ERRMsg, ERROR_LINE() AS ATLine
    END CATCH
    
    DROP TABLE #MyTable
    
    Monday, August 02, 2010 5:39 AM
  • Since you get a compilation error, the TRY block is not even entered, and thus you never enter the CATCH block.

    What is a little more fun is that you can enter the TRY block, and because of deferred name resolution enter the TRY block, but the error will still not be called in the local CATCH block. (It can still be caught in an outer TRY-BLOCK.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Monday, August 02, 2010 9:17 AM
  • Thanks for information.

    Actually in my case, the column expects the numeric value (so no single quote used), but the input value is not controlled by me and may be something invalid (not invalid numeric value), just like the update statement I posted before. So I want to add the try catch to handle this error.

    In addition, I have tried to create the stored procedure with the update statement, and execute the stored procedure in the try catch block, but the error still cannot be caught.

    Wednesday, August 04, 2010 3:24 AM
  • Still the syntax of the example given is invalid. Here is the correction, it catches:

    USE tempdb;
    SELECT * INTO Product FROM AdventureWorks2008.Production.Product
    GO
    DECLARE @abc varchar(16) = 'abc'
    
    begin try
    
    update Product set ListPrice = @abc
    
    end try
    
    begin catch
     select 'catch error'
    end catch
    GO
    DROP TABLE tempdb.dbo.Product
    

    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Wednesday, August 04, 2010 4:43 AM
    Moderator
  • steven043 had the answer to my problem which was updating a table or column that doesn't exist.  Apparently the compiler checks this regardless if you are checking it, calls it an error without ever executing it.  Apparently Microsoft tried to be smart for the developer on this one and I think it back-fired.

     

    In my case I was working on a application database upgrade where a column may or may not exist if a script was run more than once, so I put in a check:

    IF COLUMNPROPERTY(OBJECT_ID('FAU.CMC_PRPR_PROV_AU'),'WMDS_SEQ_NO', 'COLUMNID') IS NOT NULL
    	UPDATE FAU.CMC_PRPR_PROV_AU SET WMDS_SEQ_NO=NULL;
    

    Unfortunately for the "smart SQL" it was failing this because the code had already run, so the column no longer existed even though I am handling it with the IF statement.  The way I got around the so-called smart SQL was by putting the update statement in a string and executing it that way.  If it failed the Try Catch block can now catch the error.

    BEGIN TRY
    	DECLARE @sql varchar(100)
    	SET @sql = 'UPDATE FAU.CMC_PRPR_PROV_AU SET WMDS_SEQ_NO=NULL;'
    	exec @sql
    END TRY
    BEGIN CATCH
    	PRINT 'Column does not exist. No error'
    END CATCH
    

     

    • Proposed as answer by Dabid Cuedmacor Wednesday, September 29, 2010 6:43 PM
    Wednesday, September 29, 2010 6:43 PM
  • > steven043 had the answer to my problem which was updating a table or column that doesn't exist.  Apparently the compiler checks this regardless if you are checking it, calls it an error without ever executing it.  Apparently Microsoft tried to be smart for the developer on this one and I think it back-fired.

    Well, if you in C# try to access a member that does not exist, do you expect the code to compile and then blow up at run-time? (Alternatively transfer control to your catch handler.)

    What is a little strange in SQL Server is that due to deferred name resolution, compilation errors can happen at run-time, after the TRY block has been entered, and they are still not caught in the local CATCH handler.

    Note that with column names this is less likely to happen, since there is no deferred name resolution on columns. It would only happen if a column is dropped after the initial compilation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, September 29, 2010 10:19 PM