October 30, 2007 at 12:16 am
On the following store procedure, if the error occurs on the second update statement, does the table be updated?
Store procedure:
begin tran
update table1 set ClientID=@ClientID, Description=@Desc,ChangeDate=@ChangeDate where ID=@ID
if @@error<>0
begin
rollback tran
end
update table2 set Rate=@rate where Id=@ID
if @@error<>0
begin
rollback tran
end
commit tran
October 30, 2007 at 2:08 am
No. you're checking for an error directly after the update statement, and if there is an error, you're rolling the entire transaction back (everything back to the begin tran)
p.s. Since you're on SQL 2005, look at the try-catch error handling, as its a lot cleaner.
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
October 31, 2007 at 11:09 am
begin tran
update table1 set ClientID=@ClientID, Description=@Desc,ChangeDate=@ChangeDate where ID=@ID
if @@error<>0
begin
rollback tran
end
If the error occurs above, the next statement WILL execute outside of a transaction, but will error after it is committed when it tries to commit. below
update table2 set Rate=@rate where Id=@ID
if @@error<>0
begin
rollback tran
end
If the error is with the second update BOTH are rolled back.
commit tran
This will error every time either of the updates fails.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply