Transaction and Rollback Syntax

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)

Transaction and Rollback Syntax

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2003-05-16 : 17:02:05

Based upon several forum searches on the subject, I've been using transactions in my stored procedures as follows. I recently had an error on the "DELETE from tblThree WHERE....." query and got the following message: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." I've since fixed the code that caused the error, but this froze SQL Server and convinced me that something is wrong with my TRANSACTION and/or ErrorHandler syntax. I've tested putting the "if @@Error <> 0 GOTO ErrorHandler" both before (as it currently is) or after the END, but this doesn't appear to change anything. Thanks in advance for any guidance on this!

CREATE PROCEDURE spExample
@Criteria int
AS
BEGIN TRANSACTION
DELETE from tblOne WHERE Criteria = @Criteria
--Rollback transaction and exit stored procedure
if @@Error <> 0 GOTO ErrorHandler
if (@Criteria > 10)
BEGIN
UPDATE tblTwo SET .........
--Rollback transaction and exit stored procedure
if @@Error <> 0 GOTO ErrorHandler
END
else
BEGIN
DELETE from tblThree WHERE.....
--Rollback transaction and exit stored procedure
if @@Error <> 0 GOTO ErrorHandler
END
COMMIT TRANSACTION
RETURN
ErrorHandler:
ROLLBACK TRANSACTION
RETURN

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-16 : 18:35:15

What you have there looks good.
Could have variables to save the values to make it less likely someone will put code before the @@error check

declare @error int, @rowcount int
BEGIN TRANSACTION
DELETE from tblOne WHERE Criteria = @Criteria
select @error = @@error, @rowcount = @@rowcount
if @error <> 0 GOTO ErrorHandler

You have probably got an untrappable error which has exitted your batch immediately without hitting the next statement.
Most errors due to key violations in triggers for instance will do this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources