Re: try...catch execution issue

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

John Couch (JohnCouch@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
According to BOL unless you physically return in the catch, any code after
the "end catch" should execute.

Unless there is an error that dooms your transaction, and you fail to
roll it back.

I am nesting procedure calls, and logging errors, then attempting to
re-raise the error without a second logging of the error from the outer
procedure call. If I execute a procedure, an error occurs, call the
error handler, log it, -reraise it, return code works. When I call a
procedure that calls another procedure, and the inner procedure
generates an error, logs it, reraises it, the return code is not set,
and the outer catch block does not execute any code or set the return
code of the procedure.

The hour is a little late to load your tables and procedures, and since I
see that they define linked servers etc, I'm not really enthusiastic of
running your code either.

But I would suggest that when you use TRY-CATCH, you don't care about the
return code from stored procedures, nor do you care to return any
explicit return values either, but you rely on TRY CATCH only. If you
have a mix of code with old-style error handling, and new code, you
may want to add a "RETURN 4711" at the end of the catch handler.

If you can condense your issue to a somewhat smaller set of code, I'd
be interested to take a look.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Problem with bulk load security.
    ... go back read my previous post about using EXECUTE AS ... you need to impersonate a *server login*. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: Starting up database customers every minute or so in SQL LOG
    ... Did you check the initial few lines of the sql log as I mentioned?If you are ... unable to find the location of the error log, then execute the below command ... priority class 'normal'(n CPU detected) " where n indicates the number. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: proper permissions for aspnetdb membership access
    ... Windows built-in account. ... application's application pool to execute the T-SQL statements. ... Open your SQL Server Management Studio with a local ... If it does not exist in the logins of your SQL Server ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server calling a Universe Subroutine
    ... I'm just trying to run from Sql Analyzer now but eventually it will be ... see if an error is being thrown that Sql Server is hiding and I check ... I'm trying to call an .NET console exe program from a trigger in Sql ... Services calling other services or trying to execute something ...
    (comp.databases.pick)
  • Re: SQL Server calling a Universe Subroutine
    ... I'm just trying to run from Sql Analyzer now but eventually it will be ... see if an error is being thrown that Sql Server is hiding and I check ... I'm trying to call an .NET console exe program from a trigger in Sql ... Services calling other services or trying to execute something ...
    (comp.databases.pick)