ROLLBACK TRANSACTION - SQL Server Forums

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)

ROLLBACK TRANSACTION

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-06-15 : 10:02:43

I realize there are many ways to handle transactions in a Stored Proc but please tell me if my way is a good way to test for errors and then rollback and exit out of the SP. Thanks


		IF @@ERROR <> 0 
			BEGIN
				ROLLBACK TRAN
				RETURN
			END

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-15 : 10:08:49

Show us the rest of the SP, the answer is "it depends", but in principle you're right.

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-06-15 : 10:10:09

WELL THE CODE IS LONG BUT IT IS SOMETHING LIKE:

SELECT...
ERROR CHECK
UPDATE...
ERROR CHECK
SELECT...
ERROR CHECK
UPDATE...
ERROR CHECK

END STORED PROC

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-15 : 10:26:16

OK, makes sense. Another way I do it is to put a success and failure block in SPs. Example below.
Some people don't like the 'G' word (GOTO), but I find it is useful if you want to jump out as in your example, and it also lets you put any error logging in one common place, rather than after each SELECT/UPDATE etc.

DECLARE @err INT
SELECT....
SELECT @err=@@ERROR
IF @err <> 0 GOTO Rollback_Tran
UPDATE....
SELECT @err=@@ERROR
IF @err <> 0 GOTO Rollback_Tran
Commit_Tran:
COMMIT TRANSACTION
GOTO Exit_Point
Rollback_Tran:
ROLLBACK TRANSACTION
-- do something with the @err number, normally log it
RETURN @err
Exit_Point:
RETURN 0

jesus4u
Posting Yak Master

204 Posts

Posted - 2004-06-15 : 10:27:37

thanks for the imput

Kristen
Test

22859 Posts

Posted - 2004-06-15 : 10:51:54

quote:
Originally posted by jesus4u

I realize there are many ways to handle transactions in a Stored Proc

I think I can make this look hard!

We do the following:


CREATE PROCEDURE MySProc
   @intSomeParameter int,
   ....
AS
SET NOCOUNT ON

... code which does NOT do any updates ...
... set @intMyErrorRetVal to any error value ...

IF @intMyErrorRetVal = 0
BEGIN
	BEGIN TRANSACTION MySProc_01
	SAVE  TRANSACTION MySProc_02

... some UPDATE / INSERT code ...

	SELECT @intMyErrorRetVal = @@ERROR, @intRowCount = @@ROWCOUNT
	IF @intMyErrorRetVal <> 0
	BEGIN
		GOTO MySProc_ABORT
	END

... some more UPDATE / INSERT code ...
... and error checking ...

END

MySProc_ABORT:

IF @intMyErrorRetVal = 0
BEGIN
	COMMIT TRANSACTION MySProc_01
END
ELSE
BEGIN
	ROLLBACK TRANSACTION MySProc_02
	COMMIT TRANSACTION MySProc_01
END

SET NOCOUNT OFF

RETURN @intMyErrorRetVal

END

The reason for the nested transaction blocks is so that if this SProc is called from another, which also has a transaction block, then if this one rollsback the @@TRANCOUNT is not mucked up - in a way which would otherwise be intercepted by ADO etc. as a warning message

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources