Sql-server – Nested transactions in Sql Server

sql-servertransactions

Imagine the following scenario:

I am using SQL Server 2005. I have a transaction that is calling, among other SQL statements, a stored procedure that also has a transaction inside. The outer transaction sometimes fails and it is rolled back after the stored procedure is called and committed successfully.

My question is, does the stored procedure's transaction rollback too?

Best Solution

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.