Roll back across stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Roll back across stored procedures

Author

Topic

sona
Yak Posting Veteran

68 Posts

Posted - 2001-12-10 : 06:42:22

hello,
I have a SP1
{
begin transaction
..........
exec SP2
........
Commit transaction
}

In the SP2 i have an insert statement.
If the transaction fails in SP1 then is there any way that insertion done in the SP2 will rollback

I checked the above it does not allow that across SP's
Is there any other way?

thanks

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2001-12-10 : 06:52:28

You could use the built in transaction count variable (@@trancount) in a stored procedure such as this:-

CREATE PROCEDURE usp_Template
AS
DECLARE
@intErrorCode int,
@TransactionCountOnEntry int

--Error handling code
SELECT @intErrorCode = @@Error
If @intErrorCode = 0
Begin
SELECT @TransactionCountOnEntry = @@TranCount
BEGIN TRANSACTION
End

If @intErrorCode = 0
Begin
<your code goes here>
End

--Error handling code
If @@TranCount > @TransactionCountOnEntry
Begin
If @@Error = 0
COMMIT TRANSACTION --if no errors were encountered then COMMIT
Else
ROLLBACK TRANSACTION --if errors encountered then ROLLBACK

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-10 : 07:36:27

Given that the transaction is controlled from sp1 and that sp2 is included in that transaction if the transaction is rolled back on sp1 any cahnges made in sp2 will also be rolled back.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sona
Yak Posting Veteran

68 Posts

Posted - 2001-12-10 : 08:17:42

no, it is not rolling back.
i checked it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources