Error handlers? Returning from?
in [Programming SQL]
|
From: Thomas Malia on 25 Jul 2008 10:52 I'd like to write a generic block of T-SQL at the end of a long stored proc that I can "GoTo" whenever I want to log an event or an error. This way, if if I ever want to change/enhance the logging (right now I'm just inserting info into a log table but I might want to insert to a file and/or send an email, etc) I'd just have one place that I need to change to code. I was planning on using local variables to hold things like error number, message, whether the event/error should roll back current trans, whether the event/error should abort the entire procedure, etc. It's that last one there: "whether the event/error should abort the entire procedure" that has me stumped. Is there any way to "jumnp back" to the next line of the TSQL code after the point at which the GoTo statement that sent the system to the error handler was called? Basically treating the GoTo section just like a subroutine? If the event/error was not fatal to the overall process, then I want to log the event, then jump back to the original code and keep going. Any recommendations? Right now I'm assuming my best option will be to write a seperate stored proc that would handle my logging and call that instead of use a GoTo in my code. Is this the correct approach?
From: Roy Harvey (SQL Server MVP) on 25 Jul 2008 11:21 Take a look at TRY/CATCH blocks and the RAISERROR() function. Roy Harvey Beacon Falls, CT On Fri, 25 Jul 2008 10:52:40 -0400, "Thomas Malia" <tommalia(a)worldnet.att.net> wrote: >I'd like to write a generic block of T-SQL at the end of a long stored proc >that I can "GoTo" whenever I want to log an event or an error. This way, if >if I ever want to change/enhance the logging (right now I'm just inserting >info into a log table but I might want to insert to a file and/or send an >email, etc) I'd just have one place that I need to change to code. > >I was planning on using local variables to hold things like error number, >message, whether the event/error should roll back current trans, whether the >event/error should abort the entire procedure, etc. > >It's that last one there: "whether the event/error should abort the entire >procedure" that has me stumped. > >Is there any way to "jumnp back" to the next line of the TSQL code after the >point at which the GoTo statement that sent the system to the error handler >was called? Basically treating the GoTo section just like a subroutine? If >the event/error was not fatal to the overall process, then I want to log the >event, then jump back to the original code and keep going. > >Any recommendations? Right now I'm assuming my best option will be to write >a seperate stored proc that would handle my logging and call that instead of >use a GoTo in my code. Is this the correct approach? >
From: Thomas Malia on 25 Jul 2008 12:10 I'll be darnned! I didn't know T-SQL supported try catch. I'll look at that. In the mean time though, I went ahead and moved the Event/Error logging out to a seperate stored proc. which seemed OK, but I ran into one BIG problems. My code marches along just fine logging what's happening via calls to the stored proc.. EXCEPT when something happens that requires me to roll back any transactions. Unfortunately the calls to the logging proc and subsequently the inserts into the logging table are contained within the same Transactions as the code that went bad so when I roll back the transactions for the "bad code" my logs get lost along with them. How can I have my inserts to the log table happen outside of the logical transaction of the code that I'm generating the logs for? BTW, If I can stay SQL 2000 compatable I'd preffer to. "Roy Harvey (SQL Server MVP)" <roy_harvey(a)snet.net> wrote in message news:qprj84tin2q75742upn21hg9ebii72oaaj(a)4ax.com... > Take a look at TRY/CATCH blocks and the RAISERROR() function. > > Roy Harvey > Beacon Falls, CT > > On Fri, 25 Jul 2008 10:52:40 -0400, "Thomas Malia" > <tommalia(a)worldnet.att.net> wrote: > >>I'd like to write a generic block of T-SQL at the end of a long stored >>proc >>that I can "GoTo" whenever I want to log an event or an error. This way, >>if >>if I ever want to change/enhance the logging (right now I'm just inserting >>info into a log table but I might want to insert to a file and/or send an >>email, etc) I'd just have one place that I need to change to code. >> >>I was planning on using local variables to hold things like error number, >>message, whether the event/error should roll back current trans, whether >>the >>event/error should abort the entire procedure, etc. >> >>It's that last one there: "whether the event/error should abort the entire >>procedure" that has me stumped. >> >>Is there any way to "jumnp back" to the next line of the TSQL code after >>the >>point at which the GoTo statement that sent the system to the error >>handler >>was called? Basically treating the GoTo section just like a subroutine? >>If >>the event/error was not fatal to the overall process, then I want to log >>the >>event, then jump back to the original code and keep going. >> >>Any recommendations? Right now I'm assuming my best option will be to >>write >>a seperate stored proc that would handle my logging and call that instead >>of >>use a GoTo in my code. Is this the correct approach? >>
From: Roy Harvey (SQL Server MVP) on 25 Jul 2008 12:55 On Fri, 25 Jul 2008 12:10:38 -0400, "Thomas Malia" <tommalia(a)worldnet.att.net> wrote: >BTW, If I can stay SQL 2000 compatable I'd preffer to. So much for TRY / CATCH then. They came in with 2005. As for the other, I guess you have to write to the log after the ROLLBACK instead of before. Roy Harvey Beacon Falls, CT
|
Pages: 1 Prev: Linked Navision 4 server Next: Querying web service data from SQL |