July 9, 2012 at 8:12 am
In IsCompleted column of ORDER table, default value is "No".
I created a store procedure to submit an order as below.
Question: Is it necessary to add code after "Rollback tran"?
UPDATE ORDER
SET ISCOMPLETED = 'No'
-------------------------------
BEGIN
BEGIN TRAN
UPDATE ORDER
SET ISCOMPLETED = 'Yes'
SET @myerror = @@ERROR
IF @myerror <> 0
BEGIN
GOTO ERROR_HANDLER
END
COMMIT TRAN
ERROR_HANDLER:
IF @myerror <> 0
BEGIN
ROLLBACK TRAN
UPDATE ORDER
SET ISCOMPLETED = 'No'
END
END
July 9, 2012 at 8:44 am
No. There is no need to add any code after the ROLLBACK TRANSACTION statement.
The ROLLBACK TRANSACTION rolls back all DML statements between your BEGIN TRANSACTION and GOTO ERROR_HANDLER statement.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 9, 2012 at 9:23 am
Why are you using @@Error and Goto and not a try catch block?
Also, if the completed is No by default, why the update to set it to no first? Why the set to No again at the end? If the transaction rolls back, the rows will be 'No' anyway, why update them a second time?
Why update the entire table to yes?
Since it's a single statement, you don't even really need a transaction, the statement is always in a transaction of it's own and a single update will always completely succeed or completely fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply