SQL TRANSACTIONS

Hi,

I need to add place some TSQL into a TRANSACTION.  I understand the pricipal but am confused by the ROLLBACK command.

If it did

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

Is there any point in having adding a ROLLBACK statement to this code.   What would I gain from using a ROLLBACK command?

Thanks
nutnut
nutnutAsked:
Who is Participating?
 
hongjunCommented:
Since you have more than 1 action queries, you should have a transaction so that should any one of them fail, both will be rollbacked (revert back to original state) if there's any error.

hongjun
0
 
hongjunCommented:
Try this

BEGIN TRAN

DECLARE @intErrorCode INT

DELETE OrderItems WHERE order_num = 12345

SELECT @intErrorCode = @@ERROR
If @intErrorCode <> 0 GoTo ErrHandle

DELETE Orders WHERE order_num = 12345

SELECT @intErrorCode = @@ERROR
If @intErrorCode <> 0 GoTo ErrHandle

COMMIT TRAN

ErrHandle:
      IF @intErrorCode <> 0
      BEGIN
            PRINT 'Unexpected error occurred!'
            ROLLBACK TRAN
      END
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
IF  @@ERROR <> 0
BEGIN
   SELECT -201 as ErrrCode
    ROLLBACK TRANSACTION
    RETURN
END
DELETE Orders WHERE order_num = 12345
IF  @@ERROR <> 0
BEGIN
   SELECT -201 as ErrrCode
    ROLLBACK TRANSACTION
    RETURN
END
COMMIT TRANSACTION
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
hongjunCommented:
aneeshattingal,

Side track a bit.
May I ask how do you post your SQL code in "highlighted" coloring? Do you have any tools to assist you? I wish to post .NET codes highlighted.

hongjun
0
 
hongjunCommented:
I am referring to your blog :)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Actually we are not supposed to post such things here , FYI, i use Windows Live writer for Blogging.
0
 
hongjunCommented:
Thanks so much.

hongjun
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.