Purpose of nested transactions

Question

I have never understood what a nested transaction is good for. Committing a nested transaction commits nothing - it just decreases @@TRANCOUNT. And ROLLBACK rolls back everything.

BEGIN TRANSACTION
   //do an update
   BEGIN TRANSACTION
     //do an insert
   COMMIT TRANSACTION
COMMIT TRANSACTION

What is the difference with this:

BEGIN TRANSACTION
     //do an update
     //do an insert
COMMIT TRANSACTION

Please give me an example why should nested transactions be used and how they make a difference.

Answer 1

Nested transactions allows your code to call other code (SPs for instance) which uses transactions itself without actually committing your transaction when they commit.

That said, you can use safepoints to roll back inside of a transaction.

There's a CodeProject article dedicated to that.

Answer 2

IF you are having scenario in which you call one SP which contains an other SP call in it. and that inner SP is also could be called independently from your application. In that case its necessary to place inner transaction(on Inner SP) as well as on parent SP.