Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Transactions 1

Status
Not open for further replies.
Naoise

Naoise

Programmer
Dec 23, 2004
318
IE
Do transactions always have to managed in a cascading fashion ie

Code: 
BEGIN TRANSACTION

INSERT INTO tblA ....

IF @@ERROR <> 0
BEGIN
  ROLLBACK TRANSACTION
  RETURN 1
END
ELSE
BEGIN
  INSERT INTO tblB ....

  IF @@ERROR <> 0
    BEGIN
      RETURN 1
      ROLLBACK TRANSACTION
     END
  ELSE
    BEGIN
      RETURN 0
      COMMIT TRANSACTION
    END
END

Can you do something like ...

Code: 
CREATE PROCEDURE spProc1
(@Response INT OUTPUT)
AS
BEGIN

BEGIN TRANSACTION

	INSERT INTO tblExists (error) values ('test')
	
	IF @@ERROR <> 0
		BEGIN
			GOTO ERROR_HANDLER
		END
	
	INSERT INTO tblNotExists(error) values ('test')

	IF @@ERROR <> 0
		BEGIN
			GOTO ERROR_HANDLER
		END
	
	SET @Response = 0	
	COMMIT TRANSACTION

ERROR_HANDLER:
	SET @Response = 1
	ROLLBACK TRANSACTION

END
	
GO

OR can you even keep an @ErrCount and on the basis of that at the end of your procedure COMMIT or ROLLBACK?
 
Sort by date Sort by votes
I don't think the second way you posted will work. You could keep an error count though, by creating an int variable and setting it to zero.

Then, rather than rolling back if an error is encountered do this:

IF @@ERROR <> 0
BEGIN
set @errCnt = @errCnt + 1
END

Then your final piece (where you decide whether to commit or roll back) could key off of this value rather than @@ERROR.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Yes, the second code is valid. The first one have errors though :)
You can't first RETURN and then decide whet to do with transactions. I mean:
Code: 
IF @@ERROR <> 0
    BEGIN
[COLOR=red]      RETURN 1[/color]
      ROLLBACK TRANSACTION
     END
  ELSE
    BEGIN
[COLOR=red]      RETURN 0[/color]
      COMMIT TRANSACTION
    END

must be
Code: 
IF @@ERROR <> 0
    BEGIN
      ROLLBACK TRANSACTION
[COLOR=blue]      RETURN 1[/color]
     END
  ELSE
    BEGIN
      COMMIT TRANSACTION
[COLOR=blue]      RETURN 0[/color]
    END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
I didn't know you could go outside the transaction block like that. Touche.

I think I would go with the count method though, just because it is less spaghetti-ish.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Mixed signals :) ???

I tried the following :
Code: 
create table tblExists (error varchar(100))

CREATE PROCEDURE spProc1
(@Response INT OUTPUT)
AS
BEGIN

BEGIN TRANSACTION

	INSERT INTO tblExists (error) values ('test')
	
	IF @@ERROR <> 0
		BEGIN
			GOTO ERROR_HANDLER
		END
	
	INSERT INTO tblNotExists (error) values ('test')

	IF @@ERROR <> 0
		BEGIN
			GOTO ERROR_HANDLER
		END
	ELSE
		BEGIN
			GOTO SUCCESS_HANDLER
		END

ERROR_HANDLER:
	SET @Response = 1
	ROLLBACK TRANSACTION

SUCCESS_HANDLER:
	SET @Response = 0	
	COMMIT TRANSACTION
END
	
GO

CREATE PROCEDURE spProc2
AS
BEGIN

DECLARE @Response INT
EXEC spProc1 @Response OUTPUT

IF @Response <> 0
	BEGIN
		PRINT 'ERROR'
	END
ELSE
	BEGIN
		PRINT 'NO ERROR'
	END


END

GO

EXEC spProc2

and got

(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure spProc1, Line 15
Invalid object name 'tblNotExists'.
Server: Msg 266, Level 16, State 1, Procedure spProc1, Line 32
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 6, current count = 7.
NO ERROR
Server: Msg 266, Level 16, State 2, Procedure spProc2, Line 65535
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 6, current count = 7.

Have tried it a few times ;) - I don't understand why this would not work I have to say.
 
Upvote 0 Downvote
This is the problem with spaghetti code. It appears that you're running the SUCCESS_HANDLER after the ERROR_HANDLER runs. Try changing your proc to this:

Code: 
ALTER PROCEDURE spProc1
(@Response INT OUTPUT)
AS
BEGIN

BEGIN TRANSACTION

    INSERT INTO tblExists (error) values ('test')
    
    IF @@ERROR <> 0
        BEGIN
            GOTO ERROR_HANDLER
        END
    
    INSERT INTO tblNotExists (error) values ('test')

    IF @@ERROR <> 0
        BEGIN
            GOTO ERROR_HANDLER
        END
    ELSE
        BEGIN
            GOTO SUCCESS_HANDLER
        END

ERROR_HANDLER:
    SET @Response = 1
    ROLLBACK TRANSACTION
    GOTO X

SUCCESS_HANDLER:
    SET @Response = 0    
    COMMIT TRANSACTION

X:

END

I would still advocate the incrementing count method, there is really no reason to use GOTO's.

Hope this helps,
Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Upvote 0 Downvote
I wanted to use GOTO's over a count variable as the GOTO would ensure an exit from the procedure as soon as a problem occurred whereas the count variable method will have to go through the whole procedure and then at the end decide upon a COMMIT or ROLLBACK. Incidentally I ran your code with the ammendments to the stored procedure and it yielded the same results.

(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure spProc1, Line 15
Invalid object name 'tblNotExists'.
Server: Msg 266, Level 16, State 1, Procedure spProc1, Line 65535
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
NO ERROR
Server: Msg 266, Level 16, State 2, Procedure spProc2, Line 65535
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

In fact when I removed the SUCCESS_HANDLER I could see that when the first successful insert occurred it was just not being held in a transaction but being committed straight away. You can see that borne out in the order of the error statements above too. Kick myself some more or switch to a count variable, count variable it is. Maybe someone could explain why this didn't work anyway. Thanks for your help :)
 
Upvote 0 Downvote
I always "love" GOTO statement, no matter in which language :), so easy to debug :)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Ok so I have changed to using a variable to count errors. So the ROLLBACK/COMMIT is working but it doesn't seem to return a response to spProc2, results are

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Procedure spProc1, Line 18
Syntax error converting the varchar value 'test' to a column of data type int.

Any ideas why this is?


Code: 
create table tblExists (error int)

CREATE PROCEDURE spProc1
(@Response INT OUTPUT)
AS
BEGIN

DECLARE @Count INT
SET @Count = 0

BEGIN TRANSACTION

	INSERT INTO tblExists (error) values (1)
	
	IF @@ERROR <> 0
		BEGIN
			SET @Count = @Count + 1
		END
	
	INSERT INTO tblExists (error) values ('test')

	IF @@ERROR <> 0
		BEGIN
			SET @Count = @Count + 1
		END

	IF @Count > 0
		BEGIN
			SET @Response = 1
			ROLLBACK TRANSACTION
			RETURN 1
		END
	ELSE
		BEGIN
			SET @Response = 0	
			COMMIT TRANSACTION
			RETURN 0
		END
END
	
GO

CREATE PROCEDURE spProc2
AS
BEGIN

DECLARE @Response INT
EXEC spProc1 @Response OUTPUT

print @Response

IF @Response <> 0
	BEGIN
		PRINT 'ERROR'
	END
ELSE
	BEGIN
		PRINT 'NO ERROR'
	END


END

GO

EXEC spProc2
 
Upvote 0 Downvote
One thing that may be complicating things for you is the structure of committing and rollback.

Look:

Code: 
BEGIN TRAN
  BEGIN TRAN
    BEGIN TRAN
      BEGIN TRAN
      ROLLBACK TRAN
What do you think the @trancount is after this? It's 0. Rollback rolls back all the way to the very first begin tran statement, even if that statement was outside the current statement context. For example:

Code: 
CREATE PROC TranTest
AS
BEGIN TRAN
ROLLBACK TRAN
GO

BEGIN TRAN
EXEC TranTest
ROLLBACK TRAN -- Error here because the ROLLBACK TRAN inside TranTest already rolled back the tran I opened in this batch.
Now look at this:

Code: 
BEGIN TRAN
  BEGIN TRAN
    BEGIN TRAN
      BEGIN TRAN
        Insert MyTable Values('x')
      COMMIT TRAN
    COMMIT TRAN
  COMMIT TRAN
ROLLBACK TRAN
MyTable will not have a row with 'x' in it because commit tran doesn't mean anything by itself. It only means, this sub-transaction is now okay, but if the parent transaction gets rolled back, the commit won't "stick."




[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
Upvote 0 Downvote
Usually I do implement that cascading type of commit/rollback as I pointed out in my opening post but in this particular case (a much larger stored procedure) for readability I'd rather execute things inline and have a count of errors and then at the end commit or rollback.
 
Upvote 0 Downvote
I wasn't suggesting you use a cascading type of commit/rollback.

What I was pointing out was (for me) the most common source of errors about transaction count mismatch.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
Upvote 0 Downvote
and did you read SQLDenis's comment about setting transaction abort on or off?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

pdbowling
  • Locked
  • Question
View recent transactions?
Replies
1
Views
133
philhege
philhege
larrydavid
  • Locked
  • Question
Avg Transactions Per Hour Based on Timestamp 1
Replies
3
Views
121
larrydavid
larrydavid
jtarry
  • Locked
  • Question
Insert total quantity based on number of transactions
Replies
1
Views
109
markros
markros
tigerjade
  • Locked
  • Question
Why can't I use CTEs inside transactions? 1
Replies
4
Views
162
mrdenny
mrdenny
DeltaTech
  • Locked
  • Question
SQLServer transactions? 1
Replies
8
Views
144
DeltaTech
DeltaTech

Part and Inventory Search

Sponsor

Back
Top