January 13, 2009 at 1:25 pm
Ok, I've been doing some reading, and maybe I've already read the answer but I just need someone phrase the answer in a different way.
I'm using SQL Server 2000 (because my company has NIH Syndrome)
And, were basically going to be getting data from another system and sticking it into our system.
Well, this will require lots of potential updates, and inserts to different tables, so naturally I want to implement Transactions. that way if something goes wrong because maybe they feed us bad data...I can handle it properly.
If I have a stored procedure
pr_DaddyProcedure
And it has two children procedure
pr_DaughterProcedure
pr_SonProcedure
and maybe they have procedures inside them...whatever
I realize I have to check the value of @@Error after reach SQL statement.
But Would the Begin transaction and Commit transaction Be in the daddy procedure
I'm assuming the raiser error event would bubble up to the original calling Procedure?
And a rollback fired off in a child procedure would be handled correctly?
January 14, 2009 at 7:53 am
wow, I think this is the first question I asked on the board that didn't generate a quick reply.
Is my question confusing? Or is there some reason nobody uses transactions in sql server 2000?
January 14, 2009 at 12:01 pm
Transactions are definitely used in SQL 2000. I personally don't have much need for transactions so I don't have a ton of experience working them, but I'd assume you'd want your logic set up basically like:
Begin transaction inside the daddy proc.
execute son proc. Check a return from the son proc to ensure everything happened correctly.
If so, proceed to daughter, if not rollback the whole thing.
Repeat for Daughter.
Commit it both were successful.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply