How can I guarantee that nested transactions are made independently of each other?

Question

If I have a stored procedure that executes another stored procedure several times with different arguments, is it possible that each of these calls makes independently of each other?

In other words, if the first two executions of the nested procedure are successful, but the third does not succeed, is it possible to save the results of the first two executions (rather than throwing them back)?

I have a stored procedure defined in SQL Server 2000 something like this:

CREATE PROCEDURE toplevel_proc .. AS BEGIN ... while @row_count <= @max_rows begin select @parameter ... where rownum = @row_count exec nested_proc @parameter select @row_count = @row_count + 1 end END 

Answer 1

First, there is no such thing as a nested transaction in SQL Server

However, you can use SAVEPOINTs according to this example (too long to reproduce this sorry) from user SO of user Remus Rusan

Edit: Alex Kuznetsov mentioned (although he deleted his answer) that this will not work if the deal is doomed. This may happen with SET XACT_ABORT ON or some startup errors.

Answer 2

From BOL:

TRANSACTION ROLLBACK without database_name or contact_name returns to the beginning of the transaction. When a transaction is nesting, the same expression returns all internal transactions, the most external transaction is a BEGIN statement.

I also found the following from another thread here :

Remember that SQL Server transactions are not really nested in what you might think. Once a transaction followed by BEGIN TRAN increments @@ TRANCOUNT while COMMIT decreases the value. All external transaction when COMMIT results in zero @@ TRANCOUNT. But ROLLBACK without backup rolls back all the work, including the most external transaction.

If you need nested transaction behavior, you need to use SAVE TRANSACTION instead of BEGIN TRAN and use ROLLBACK TRAN [username] instead of ROLLBACK TRAN.

Thus, it is possible.