8

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

In other words, if the first two executions of the nested procedure succeed, but the third one fails, is it possible to preserve the results of the first two executions (and not roll them back)?

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

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
Improve this question

2 Answers 2

Reset to default
8

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

However, you can use SAVEPOINTs as per this example (too long to reproduce here sorry) from fellow SO user Remus Rusanu

Edit: AlexKuznetsov mentioned (he deleted his answer though) that this won't work if a transaction is doomed. This can happen with SET XACT_ABORT ON or some trigger errors.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

From BOL:

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.

I also found the following from another thread here:

Be aware that SQL Server transactions aren't really nested in the way you might think. Once an explict transaction is started, a subsequent BEGIN TRAN increments @@TRANCOUNT while a COMMIT decrements the value. The entire outmost transaction is committed when a COMMIT results in a zero @@TRANCOUNT. But a ROLLBACK without a savepoint rolls back all work including the outermost transaction.

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

So it would appear possible.

Improve this answer

2 Comments

"use ROLLBACK TRAN instead of ROLLBACK TRAN" ??
@Greg - thanks for pointing that out. The text I quoted used angle brackets around the savepoint_name text and that caused the SO markup engine to hide the text. Have edited the text to use square brackets.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.