Using Rollback Transaction in a stored procedure

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)

Using Rollback Transaction in a stored procedure

Author

Topic

collegeguy
Starting Member

4 Posts

Posted - 2002-01-16 : 08:11:55

I wish to know the correct syntax for rolling back a transaction in an errorlabel. If I do a If @@Error < 0 goto ErrorLabel

it does not do a rollback in the errorlabel.
Instead it gives me the following error:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Help,
C

llege Guy

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-16 : 08:13:49

Please tell me that you're using BEGIN TRANSACTION at the start of your code.....without that of course it's not going to roll back your trans. because there's nothing to roll back.

Mike
"A program is a device used to convert data into error messages."

collegeguy
Starting Member

4 Posts

Posted - 2002-01-17 : 01:15:30

Here is my stored procedure. It allows me to insert the first row but does not rollback.

That is causing a problem.

Create Procedure am_MoneyTransfer
(@member1 int,
@member2 int,
@amount int,
@success char(1) output)
As
Declare @balance int
Exec am_balancecheck @member1, @balance out
if @balance >= @amount
begin
begin transaction
Insert Into transactions(memberacno, moneyout, trstatus, trdesc) values(@member1,@amount,'C','Given to '+ Cast(@member2 as varchar))
Insert Into transactions(memberacno, moneyin, trstatus, trdesc) values(@member2,@amount,'C','Taken From '+ Cast(@member1 as varchar))

if @@error < 0 goto errorlabel
else
commit transaction
end
else
print 'Insufficient funds'
ErrorLabel:
Rollback Transaction

print 'Wrong Member a/c selected'

Regards,
C

llege Guy

Lavos
Posting Yak Master

200 Posts

Posted - 2002-01-17 : 01:33:21

Well, there's a couple of problems.

Rollback trans will rollback all transactions to the last "Savepoint". This isn't allowed in a stored procedure and will generate an error if you call the proc from within a transaction already. Look in the books online for "Save Transaction" and @@TRANCOUNT for an example of how to use transactions inside a stored procedure. I believe this also applies to triggers.

Your if logic is also messed up. Rollback trans is always going to be called AFAIK, because that final else is missing a BEGIN-END pair. It is possible for rollback trans to be called without the begin trans if @balance is less than @amount.

You probably want to setup the transaction right before your first if statement, then make sure that either a roll back to the save point or commit trans happens in every logical path. (As a note, use IF (@@ERROR = 0) COMMIT TRANS instead of using an inequality operator tp branch to the error statement. IIRC, the errors are usually positive numbers to.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-01-17 : 08:00:38

You could try something like this below, not only will it ensure commit or rollback depending on errors but will also ensure count the transactions to ensure they begin and end.

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
End

Edited by - knookie on 01/17/2002 08:02:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources