Lets say this is the situation:
[Stored Proc 1]
BEGIN
BEGIN TRANSACTION
...
exec sp 2
COMMIT
END
Now, if SP 2 - rolls back for whatever reason, does SP 1 - commit or rollback or throw exception?
Thanks.
Lets say this is the situation:
[Stored Proc 1]
BEGIN
BEGIN TRANSACTION
...
exec sp 2
COMMIT
END
Now, if SP 2 - rolls back for whatever reason, does SP 1 - commit or rollback or throw exception?
Thanks.
In nested transactions, if any of the inner transations rolls back, all its outer transaction will rollback.
It is possible for the work done by SP2 to be rolled back and not loose the work done by SP1. But for this to happen, you must write your stored procedures using a very specific pattern, as described in Exception handling and nested transactions:
Not all errors are recoverable, there are a number of error conditions that a transaction cannot recover from, the most obvious example being deadlock (your are notified of the deadlock exception after the transaction has already rolled back). Both SP1 and SP@ have to be written using this pattern. If you have a rogue SP, or you want to simple leverage existing stored procedures that nilly-willy issue
ROLLBACK
statements then your cause is lost.Here is a quick and dirty way to nest transactions in stored procedures (using the code from Aaron's answer) that can be useful sometimes. It uses a default parameter to indicate to the inner procedure if it is a nested call, and returns a success/fail result to the outer procedure.
The outer procedure checks the success/fail an rolls back the transaction if appropriate.
If SP2 rolls back the transaction, SP1 will rollback as well.
See: http://msdn.microsoft.com/en-US/library/ms187844(v=sql.105).aspx for details.
There are no autonomous transactions in SQL Server. You may see
@@TRANCOUNT
increase beyond 1, but a rollback affects the whole thing.EDIT asked to point to documentation. Don't know of the topic that documents this explicitly, but I can show it to you in action.
Inner proc:
Outer proc:
So now let's call it and let everything commit:
Results:
Now let's call it and roll back the inner procedure:
Results: