locked
try ... catch issue RRS feed

  • Question

  • hi friend

    i designed a very simple sp block here like,

    SCENARIO : 1
    ---------------

    create proc pro as
    BEGIN
    BEGIN TRY
    SELECT  * from tmptmt
    END TRY
    BEGIN CATCH
    SELECT ERROR_LINE(),ERROR_MESSAGE() ,ERROR_NUMBER(),ERROR_PROCEDURE(),ERROR_SEVERITY(),ERROR_STATE()
    END CATCH
    END

    now as per my concept whenever a error raise the block should moove to CATCH block and do what ever is written there ( print error message , number ..etc)
    but when i exected this procedure "exec pro" it returns me

    Msg 208, Level 16, State 1, Procedure pro, Line 5
    Invalid object name 'tmptmt'.


    when try...catch is there then why erorr rose, why ERROR_LINE(),ERROR_MESSAGE() ...etc in CATCH block get output there.


    now the scenario becomes more confusing when i changed the Procedure little bit like


    SCENARIO : 2
    ---------------

    alter proc pro as
    BEGIN
    BEGIN TRY
    SELECT  1/0
    END TRY
    BEGIN CATCH
    SELECT ERROR_LINE(),ERROR_MESSAGE() ,ERROR_NUMBER(),ERROR_PROCEDURE(),ERROR_SEVERITY(),ERROR_STATE()
    END CATCH
    END

    now somehow its working fine like when i execute the "exec pro" command it returns me two output
    like

    -----------

    (0 row(s) affected)

                                                                                                                                                                                                                                                                                                                                                                                                                                         
    ----------- ---------------------------------  ---------- ------  ---- -----
    4           Divide by zero error encountered.   8134        pro   16   1

    (1 row(s) affected)



    here second output is fine but why this fist output is here when Try and Catch block is ready to handel this, and in first scenariot why catch block is not being read

    please enlighten me with your kind responce.

    regards,

    Shanu


    Regards, Shanu
    Wednesday, September 21, 2011 7:00 AM

Answers

  • Hi,
    Any compile errors (like syntax errors) or object name errors that occurs during deferred name resolution do not invoke a CATCH block.

    You can see all SQL error text and severity level.
    select * from sys.messages

    Shatrughna
    • Marked as answer by Shanu_Shri Wednesday, September 21, 2011 11:54 AM
    Wednesday, September 21, 2011 7:47 AM
  • HI Shanu_Shri !
     
    The reason TRY / CATCH doesn't catch the error in your first scenario is clearly mentioned in the MSDN provided by Microsoft. See it below;

    A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

    Scenarios you have mentioned here are already discussed in MSDN, please take a look into this article;

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

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    • Marked as answer by Shanu_Shri Wednesday, September 21, 2011 11:55 AM
    Wednesday, September 21, 2011 10:18 AM
    Answerer
  • so above all should i consider this the shortcoming of SQL Server,

    I used think it that it was a shortcoming, but I'm getting more and more convinced that it is a conscious design. They simply don't want it to be too easy for us.

    Pardon my sarcasm, yes, this is a limitation.

    and in second scenario (as i mentioned in my Question ) why the first output(column with no value) is coming out when try block already routed the error to catch block.

    This is somewhat easier to understand. Let's take a different example:

    SELECT a/b FROM tbl

    Assume that tbl has thousands of rows. Assume further on one the last rows that SQL Server reads, b is = 0. What happens here is that the client gets the result set of all (or most of) the rows access before the division.

    Is this correct or not? That can be disputed, but if SQL Server would compute the entire result set and only return it, if there no errors occur while evaluating it, SQL Server would have to buffer the result set somewhere and then return results from the buffer. This also means that for a huge result set, the client will have to wait. This in conflict with a good user experience in many cases. Even if you are retrieving lots of rows, you still want the first ones quick if possible, so you can look at the data.

    You could say that in this cases there are conflicting interest, and there is a trade-off.

    (I should add that in some cases, this buffering is what happens anyway, to wit if the query plan includes a stopping operator like a Sort.)

    please advice me what type of code i should put into TRY ..CATCH and what type of code shouldnt.

    About everything, unless you have a very good reasons. Yes, there are limitations, but that does not motivate throwing about both baby and bathtub.

    See the link Uri posted, for a template for your error handling.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Shanu_Shri Wednesday, September 21, 2011 11:54 AM
    Wednesday, September 21, 2011 10:43 AM

All replies

  • begin try
    exec pro
    end try 
    begin catch
    SELECT ERROR_LINE(),ERROR_MESSAGE() ,ERROR_NUMBER(),ERROR_PROCEDURE(),ERROR_SEVERITY(),ERROR_STATE()
    end catch

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 21, 2011 7:14 AM
    Answerer
  • Compile errors  are not handled by try catch block

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


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Wednesday, September 21, 2011 7:16 AM
  • In their infinite wisdom, Microsoft decided since error handling always has been a difficult point in SQL Server, we should become too spoiled when we got TRY-CATCH, so they added some gotchas.

    One of these gotchas is that compilation error that happens at run-time because of deferred name resolution cannot be caught in the scope where they occur, only in outer scope. Try this:

    CREATE PROCEDURE inner_sp AS
    BEGIN TRY
         PRINT 'This prints'
         SELECT * FROM notexists
    END TRY
    BEGIN CATCH
         PRINT 'This does not print'
    END CATCH
    go
    CREATE PROCEDURE outer_sp AS
    BEGIN TRY
         EXEC inner_sp
    END TRY
    BEGIN CATCH
         PRINT 'Here is the error message: ' + error_message()
    END CATCH

    This Connect item is closed as Won't Fix, but you can still add a protest vote:
    https://connect.microsoft.com/SQLServer/feedback/details/496758/try-catch-should-capture-the-parse-errors


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, September 21, 2011 1:20 PM
    Wednesday, September 21, 2011 7:17 AM
  • i dont this is a solution , why i need this when i have done all error management in to the block and what if i call this proc into application and want to catch the error line, error severity error message etc at database level.

     


    Regards, Shanu
    • Edited by Shanu_Shri Wednesday, September 21, 2011 7:32 AM
    Wednesday, September 21, 2011 7:18 AM
  • Well ,then please read Erland explanation about how MS handles errors
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 21, 2011 7:26 AM
    Answerer
  • Hi,
    Any compile errors (like syntax errors) or object name errors that occurs during deferred name resolution do not invoke a CATCH block.

    You can see all SQL error text and severity level.
    select * from sys.messages

    Shatrughna
    • Marked as answer by Shanu_Shri Wednesday, September 21, 2011 11:54 AM
    Wednesday, September 21, 2011 7:47 AM
  • so above all should i consider this the shortcoming of SQL Server, and in second scenario (as i mentioned in my Question ) why the first output(column with no value) is coming out when try block already routed the error to catch block.

     

    please advice me what type of code i should put into TRY ..CATCH and what type of code shouldnt.

     

    willing for your more enlightenment

     

    thanks


    Regards, Shanu
    • Edited by Shanu_Shri Wednesday, September 21, 2011 7:59 AM
    Wednesday, September 21, 2011 7:51 AM
  • http://www.sommarskog.se/error_handling_2005.html
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 21, 2011 8:02 AM
    Answerer
  • HI Shanu_Shri !
     
    The reason TRY / CATCH doesn't catch the error in your first scenario is clearly mentioned in the MSDN provided by Microsoft. See it below;

    A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

    Scenarios you have mentioned here are already discussed in MSDN, please take a look into this article;

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

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    • Marked as answer by Shanu_Shri Wednesday, September 21, 2011 11:55 AM
    Wednesday, September 21, 2011 10:18 AM
    Answerer
  • so above all should i consider this the shortcoming of SQL Server,

    I used think it that it was a shortcoming, but I'm getting more and more convinced that it is a conscious design. They simply don't want it to be too easy for us.

    Pardon my sarcasm, yes, this is a limitation.

    and in second scenario (as i mentioned in my Question ) why the first output(column with no value) is coming out when try block already routed the error to catch block.

    This is somewhat easier to understand. Let's take a different example:

    SELECT a/b FROM tbl

    Assume that tbl has thousands of rows. Assume further on one the last rows that SQL Server reads, b is = 0. What happens here is that the client gets the result set of all (or most of) the rows access before the division.

    Is this correct or not? That can be disputed, but if SQL Server would compute the entire result set and only return it, if there no errors occur while evaluating it, SQL Server would have to buffer the result set somewhere and then return results from the buffer. This also means that for a huge result set, the client will have to wait. This in conflict with a good user experience in many cases. Even if you are retrieving lots of rows, you still want the first ones quick if possible, so you can look at the data.

    You could say that in this cases there are conflicting interest, and there is a trade-off.

    (I should add that in some cases, this buffering is what happens anyway, to wit if the query plan includes a stopping operator like a Sort.)

    please advice me what type of code i should put into TRY ..CATCH and what type of code shouldnt.

    About everything, unless you have a very good reasons. Yes, there are limitations, but that does not motivate throwing about both baby and bathtub.

    See the link Uri posted, for a template for your error handling.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Shanu_Shri Wednesday, September 21, 2011 11:54 AM
    Wednesday, September 21, 2011 10:43 AM
  • hi everyone ,

    many many thanks for providing my that much informatin regarding the issue and enlighten me with your valuable and detailed suggestion. now i am quite clear with the concepts.

     

    thanks again :)


    Regards, Shanu
    Wednesday, September 21, 2011 11:56 AM