question

nileshamruskarsqlserver avatar image
nileshamruskarsqlserver asked

Atomic Operation

I have 2 sp's, SP 'B' is called after SP 'A'. SP 'A' delete's the data from Table XYZ,Then SP B Insert's Data in Table XYZ. Suppose, due to some exception SP 'B' fails then I have implemented a logic which will rollback the transaction's. But the challenge is SP 'A' has ran already and it has deleted the data from Table XYZ. Ultimately I am left with no data. How can be avoid loosing data? how to implement automic operation. Cant Merge 2 Sp's in 1, both run's on the basis of conditions..
transactiondml
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
Then you need to wrap both calls in a transaction - either another procedure around them, or in the calling application
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@kev: Sorry, I am bit confused with your suggestion.. Can u elaborate in detail. My primary concern is to commit the transaction only when SP 'B' is ran successfully.
0 Likes 0 ·
So from your calling application (I don't know what that is) - you could start a transaction, execute SP 'A', execute SP 'B', and then rollback or commit depending on the outcome of SP'B'. Alternatively create a new stored procedure 'C' that wraps the execution of 'A' and 'B' together, and use transaction control within 'C' to rollback or commit. In both cases you are moving the transaction control to be around 'A' and 'B' together, rather than it just being in SP 'B' as you have described.
0 Likes 0 ·
Appreciate for your response and assistance.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.