none
TRY CATCH exit Statements in CATCH RRS feed

  • Question

  • Hi Forum,

    I have a couple of SQL-statements, each in a try catch block. In each catch block I do my staement specific error handling. How can I avoid that the next statements are executed if one of the statements fails? Or do I just put try catch around all statements with nothing in the outer catch?

    TIA
    Alex

    Friday, May 7, 2010 8:10 AM

Answers

  • Hi Alex

    you can use Return in the Catch to end the statement. Using the example above it will look like this:

    BEGIN TRY
     SELECT 1/0
    END TRY
    BEGIN CATCH
      PRINT 'I AM IN CATCH'
      Return
    END CATCH
    PRINT 'this will not show as return has ended the process'

    At retun the process ends - so make sure it goes after any error logging in the Catch statement.

    Seth


    http://lqqsql.wordpress.com
    Monday, May 10, 2010 10:07 AM
  • Affirmative.

    If the error is properly caught by CATCH, execution continues in CATCH. Demo follows.

    BEGIN TRY
     SELECT 1/0
    END TRY
    BEGIN CATCH
     PRINT 'I AM IN CATCH'
    END CATCH
    PRINT 'I AM CONTINUING AFTER CATCH'
    /*
    (0 row(s) affected)
    I AM IN CATCH
    I AM CONTINUING AFTER CATCH
    */

    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Friday, May 7, 2010 9:34 AM
    Moderator
  • The possibility is to have a flag set in CATCH block and control the logic flow checking this flag. I always use such logic in complex code (doesn't matter which language) as I don't like multiple returns in the procedure/function (though sometimes it's easier to do returns than nested flag checks).

    So, in simple case

    begin try

      some statements

    end try

    catch 

       set @ErrorFlag = 1

      ...

    end catch

    if @ErrorFlag = 1

      return

     

     

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, May 10, 2010 1:10 PM
    Moderator

All replies

  • It depends on the nature of failure. On fatal failure nothing else is executed.

    On "soft" failure, logic continues as usual according the structure of TRY-CATCH.

    Related article link:  Catching All Errors Not Caught by TRY...CATCH

    http://www.sqlusa.com/articles2008/trycatch/


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Friday, May 7, 2010 8:30 AM
    Moderator
  • Thanks for the answer.

    So if I put a try catch around all my try catch blocks, will the execution, if an error occurs in the second block, continue with the third block or will it jump to the outer catch?

    Alex

    Friday, May 7, 2010 8:42 AM
  • Affirmative.

    If the error is properly caught by CATCH, execution continues in CATCH. Demo follows.

    BEGIN TRY
     SELECT 1/0
    END TRY
    BEGIN CATCH
     PRINT 'I AM IN CATCH'
    END CATCH
    PRINT 'I AM CONTINUING AFTER CATCH'
    /*
    (0 row(s) affected)
    I AM IN CATCH
    I AM CONTINUING AFTER CATCH
    */

    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Friday, May 7, 2010 9:34 AM
    Moderator
  • Hi Alex

    Please read this I think this is helpful for you

    http://www.campbellassociates.ca/blog/PermaLink.aspx?guid=823bdca5-ec17-4689-ac0c-e653664164bf

     

    Thanks

    Ramesh Rathod

    Friday, May 7, 2010 10:28 AM
  • So is there a way from preventing the execution of the code that follows the end catch? In my case it makes no sense to continue execution, if any error has already occured? Something like a 'return' in C#?

    The only other way I could think of, is having all my code in a single try catch and using a variable set before each execution step in order to be able having a specific logging to each execution step.

    Alex

    Monday, May 10, 2010 8:07 AM
  • Hi Alex

    you can use Return in the Catch to end the statement. Using the example above it will look like this:

    BEGIN TRY
     SELECT 1/0
    END TRY
    BEGIN CATCH
      PRINT 'I AM IN CATCH'
      Return
    END CATCH
    PRINT 'this will not show as return has ended the process'

    At retun the process ends - so make sure it goes after any error logging in the Catch statement.

    Seth


    http://lqqsql.wordpress.com
    Monday, May 10, 2010 10:07 AM
  • The possibility is to have a flag set in CATCH block and control the logic flow checking this flag. I always use such logic in complex code (doesn't matter which language) as I don't like multiple returns in the procedure/function (though sometimes it's easier to do returns than nested flag checks).

    So, in simple case

    begin try

      some statements

    end try

    catch 

       set @ErrorFlag = 1

      ...

    end catch

    if @ErrorFlag = 1

      return

     

     

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, May 10, 2010 1:10 PM
    Moderator