ROLLBACK IN A STORED PROCEDURE

How can i successfully execute rollback in a stored procedure in Sql S

Page 1 of 1

1 Replies - 2593 Views - Last Post: 29 November 2008 - 06:28 AM Rate Topic: -----

#1 himanshu.tomar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 13-October 08

ROLLBACK IN A STORED PROCEDURE

Posted 29 November 2008 - 12:24 AM

Hi Guys,
I am facing a problem while doing the rollback in sql server 2000 using VB.Net.
By using the conventional Method::

Dim transaction as SqlTransaction
SetConnection()
transaction = Con.BeginTransaction
</ sample code/>
transaction.commit
and getting error
transaction.rollback
But the above method is not working in my code.
I tried the code written below but is not able to implement in stored procedure.

begin transaction
insert into delete_demo values(7,'Aar3')
insert into delete_demo values(2,'Aakr')
if(@@error<>1)
commit transaction
else
rollback transaction

Any help is most welcomed....

Is This A Good Question/Topic? 0
  • +

Replies To: ROLLBACK IN A STORED PROCEDURE

#2 dineeshd  Icon User is offline

  • member icon

Reputation: 38
  • View blog
  • Posts: 619
  • Joined: 30-June 08

Re: ROLLBACK IN A STORED PROCEDURE

Posted 29 November 2008 - 06:28 AM

A typical SQL Server Transaction statement should be as below..

Begin transaction MyTrans
	
/* Your code here */

if (@@error <> 0)
begin
	print 'Modification failed!'
	goto on_transaction_failed
end

Commit Transaction MyTrans

print 'The execution completed successfully'

goto on_complete

on_transaction_failed:

begin		
	rollback transaction trans
	print 'Rolled back successfully'
	goto on_complete
end

on_complete:

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1