October 1, 2002 at 8:30 am
This is straight from books online, but I think this explains your issue:
Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 2, 2002 at 2:45 am
The little tricks that I’ve found yesterday, is use the @@TRANCOUNT.
Make a rollback only if it’s equal to 1. It means that you are on the outer level and you can make a rollback.
If you are on a higher level than 1, it means that you are encapsulated by another transaction. You make a commit. It’s really crazy because only the commit decrease the @@TRANCOUNT. But if you do that, each time you run a stored procedure, you must check the return value or the error output parameters and if it’s an error you rollback the outer transaction.
If I well understand rollback put the @@TRANCOUNT to 0 even if it’s equal to 2 or 3. And when you quit the stored procedure, the system check if the @@TRANCOUNT is the same when you enter it.
You can see an example of the code below
Do you think that it'll be possible to create a savepoint directly after I create the transaction and if I have an error I make a rollback to the savepoint and every time I leave the stored procedure, I make a commit? If it allows in the innertransaction, it will solve my problem.
-- -----------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TEST1]
GO
CREATE PROCEDURE TEST1
@ERROR INT OUTPUT
AS
BEGIN TRAN TOTO
PRINT 'TRAN COUNT'
PRINT @@TRANCOUNT
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRAN TOTO
SET @ERROR = 123
END
ELSE
BEGIN
COMMIT TRAN TOTO
SET @ERROR = 456
END
RETURN(-1)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TEST2]
GO
CREATE PROCEDURE TEST2
@ERROR INT OUTPUT
AS
PRINT 'BEFORE BEGIN TRAN TRAN COUNT'
PRINT @@TRANCOUNT
BEGIN TRAN COCO
PRINT 'AFTER BEGIN TRAN TRAN COUNT'
PRINT @@TRANCOUNT
EXEC TEST1 @ERROR OUTPUT
IF @ERROR <> 0
BEGIN
Print 'Erreur dans sous requete'
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRAN COCO
SET @ERROR = 123
END
ELSE
BEGIN
COMMIT TRAN COCO
SET @ERROR = 456
END
RETURN(-1)
END
SET @ERROR = 0
RETURN(0)
GO
-- Test Script
DECLARE @ERROR INT
EXEC TEST2 @ERROR OUTPUT
PRINT @ERROR
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply