asked on

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

hongjun

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

Aneesh

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

ASKER CERTIFIED SOLUTION

hongjun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hongjun

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

hongjun

I am referring to your blog :)

Aneesh

Actually we are not supposed to post such things here , FYI, i use Windows Live writer for Blogging.

hongjun

Thanks so much.

hongjun