Updating all my 1000 procs

  • Hi All

    I plan to update all my around 1000 procs for error handling. Currently none of them have a TRY CATCH Block.I dont want to change them all manually.

    I found that its relatively easy to add CATCH Block using TSQL script,since I can directly add it at end of all my procs.

    But I am not so sure about adding the line 'BEGIN TRY'. I have to add it after 'AS' clause of every proc. But I dont find it fullproof to find out that 'AS'. What if proc name has 'as' word? what if there is a 'as' word in some comment block above ALTER PROC?

    Any sure shot way for this to figure it out?

    Although manual way looks more reliable here.....

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Proabably it's not what you wanted to hear, but I think you'b better do it manually.

    If you are confident enough that it can be done with a script, you could open a cursor on sysobects + OBJECT_DEFINITION(@objectid) and use the REPLACE function, but I think you'll get a terrible mess this way.

    -- Gianluca Sartori

  • well, before you go and do that, read this thread, where the original poster is having trouble because his procs don't work the same way anymore when TRY...CATCH is added.

    Try Catch alters behaviour of existing procedures

    anyway, if it were me, and I was already sure TRY CATCH would add a benefit to the process, I would script them out and change them in an enhanced text editor like EditPlus or NotePad++ or UltraEdit, which have soem ehnaced find-and-replace features like using regular expressions and stuff.

    also remember to keep it simple, if there's no PROBLEM to resolve with TRY CATCH, then don't make any changes... TRY CATCH behaves a bit different, and was added to help with certain situations, not as a replacement for previous coding methods.


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 100% agree. Don't use SQL to manipulate the procedure text.

    -- Gianluca Sartori

  • In addition to doing it manually (and I can tell you a couple horror stories about times when I thought I was clever enough to do something like this via the system tables), you need to do careful testing of all to ensure that the behaviour isn't changed, that errors which will now be cause but weren't before are correctly dealt with, that transactions are correctly committed/rolled back depending on the error, etc.

    This is not a small project.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thnk for your valuable inputs. After reading all this, doing it in one shot thru script has become a strict no-2 for me. So I am thinking of doing it one proc at a time and analyze if script did it right.(yeah, i still think of using script with care. Else it will become huge manual work).

    Sure I need to check for behavior changes and the way errors will be captured now.

    Above all, shall our application be getting any benefit out of it.

    Thanks again...

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • parsing TSQL fully is hard

    but if you limit yourself to the section up to the first (uncommented, unquoted) AS in DDL statements for P, TR, FN, IF, TF modules

    it is very easy

    although possible to write a working parser in TSQL I would recommend using CSharp / VB.NET instead

    you have to write a parser that is aware of quoted blocks such as 'hello' "hello" [hello] and comments --hello /*hello*/

    which isn't very hard - It took me about 40 minutes

    it is also best to alter the objects in the correct order

    which means calculating a dependency tree and working in leaf to root order in layers

    you can do it the other way around but it is better not to

    as it either creates a total mess of the dependency data or works the box harder than it needs to

    depending on your server version

    once you have done that you will end up with some ridiculous syntax like:

    begin try

    update blah

    if @@error <> 0 or @@rowcount < 1 goto label_rollback




    rollback tran

    end try

    which no longer makes much sense as you need to change the if @@error <> 0 or @@rowcount < 1 goto label_rollback into if @@rowcount < 1 raiserror('@@rowcount < 1', 16, 1)

    and drop the label_rollback stuff

    and if any part of your logic relies on the resumptive codepath being taken ... it may not work any more

    you also have to rethrow in every catch block - so the calling module knows something went wrong

    in other words - even if you automate the process you will still need to read and fix every procedure

    my plan is to reliably calculate the module dependency tree

    and then update small groups of procedures (so it is testable) by hand

    and incrementally update the dev/qa and production servers over a period of weeks

    that reduces the risk of a big bang and downtime etc.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply