Updating all my 1000 procs

Question

Updating all my 1000 procs

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
July 9, 2010 at 5:46 am

#100272

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.

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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.

Lowell

--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!

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 5

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.

DataDog Hall of Fame Points: 3305 More actions · Answer 6

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

...

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.