Nested transactions in Sql Server?

With a nested transaction, a commit does not write any changes to disk, except for the top level transaction. A rollback, however works regardless of the level of the transaction, so yes, it will roll the inner transaction back.

Absolutely yes, the top level transaction will own all the data changes until it is committed or rolled back. However I would encourage you to think carefully about the transaction model. The more such scenarios exist in your system the greater your exposure to locking issues.

Also the computational expense of the procedure increases. It's remarkable how often, when rationalising SQL, I find transactions have been implemented where they just aren't required. I encourage you (and anyone working with transactions) to think carefully about why you are using them in each context and what would happen were the transaction not implemented.

Just my 2c worth!

Yes the stored procedure will be rolled back. Here is the overall flow of your code: BEGIN TRY BEGIN TRANSACTION EXEC SotredProcedureName --Do some other activity COMMIT TRANSACTION END TRY BEGIN CATCH --IF an error occurs then rollback the current transaction, which includes the stored procedure code. ROLLBACK TRANSACTION END CATCH Cheers, John.

I've tried with begin tran and commit inside the stored procedure say usp_test. Exec these sp with some other query as below update x set name='xxx' select * from x---contains 'xxx' begin tran update x set name='yyy' select * from x---contains 'yyy' exec usp_test select * from x---contains 'zzz' inside the sp rollback tran While executing the above query name in x table must be 'xxx' its not 'zzz' since the first begin tran rollbacked even the sp tran commit. So, first begin tran own the data changes.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.