February 12, 2008 at 8:06 pm
Hi,
Our old-timer developer is always finding faults in our queries. When we correct it, another one will be found. Can you please advise me if 'Begin Tran' and 'Commit Tran' part is wrong when used in an 'INSERT' command?
Also, why did she told me not to use 'GOTO' command, is it obsolete?
Thanks
SET XACT_ABORT ON
SET NOCOUNT ON
BEGIN TRAN
select column1, column1
into #tempTable1
from thisTableX
where condition here
if @@error != 0 GOTO rollbackThis
insert #tempTable1
select column1,column2
from thisTableY
where condition here
if @@error != 0 GOTO rollbackThis
update #tempTable1
set column1=value
from #tempTable1
where condition here
if @@error != 0 GOTO rollbackThis
GOTO CommitNow
rollbackThis:
Rollback Tran
CommitNow:
Commit Tran
February 12, 2008 at 9:25 pm
As far as goto is concerned read:
http://en.wikipedia.org/wiki/Spaghetti_code
Usually an IF statement can handle most of what GOTOs can, and it is generally considered bad practice.
As far as the transaction is concerned, I guess you need to ask yourself whether or not the situation really warrants a transaction. I generally use them when its an "all or nothing" deal. In other words, I need to modify records in several tables, and an error part way would leave the data in such a state as to be unusable. In your case it looks like you're inserting into a temp table, and I wouldn't generally consider that transaction worthy, because its getting blown away at the end of your proc anyway.
Just my two cents.
February 12, 2008 at 9:25 pm
the Command SET XACT_ABORT ON makes error handling superfoulous.
This command rolls back the transaction, AND DISCONTINUES FURTHER PROCESSING, if any error is encountered....
so if any error is encountere,d it would never go tot eh next statement, which is evaluating @@error.
the error handing that is in place would only help if you were raising your own low level errors in a trigger or something...errors 16 and above, (violation of constraints, whether FK,PK,Unique) would stop processing without moving to the @@error evaluation as well.
Lowell
February 13, 2008 at 2:09 am
Thanks for the informative advise.:)
February 14, 2008 at 8:24 am
Generally, I like to return a specific error message back to the application to identify where the failure occured.
I have two choices:
# 1
DECLARE @li_RetCode int
SET @li_RetCode = -666
BEGIN TRAN
UPDATE SomeTable SET SomeField ...
IF @@ERROR <> 0 SET @li_RetCode = -1 ELSE SET @li_RetCode = 0
IF RetCode <> 0 GOTO End_Transaction
UPDATE SomeOtherTable SET SomeOtherField ...
IF @@ERROR <> 0 SET @li_RetCode = -2
IF RetCode <> 0 GOTO End_Transaction
other code
End_Transaction:
IF @li_RetCode = 0 COMMIT TRAN
ELSE ROLLBACK TRAN
RETURN @li_RetCode
#2
DECLARE @li_RetCode int
SET @li_RetCode = -666
BEGIN TRAN
UPDATE SomeTable SET SomeField ...
IF @@ERROR <> 0 SET @li_RetCode = -1 ELSE SET @li_RetCode = 0
IF RetCode = 0 BEGIN
UPDATE SomeOtherTable SET SomeOtherField ...
IF @@ERROR <> 0 SET @li_RetCode = -2
END
IF RetCode = 0 BEGIN
some other code
IF @@ERROR <> 0 SET @li_RetCode = -3
END
IF @li_RetCode = 0 COMMIT TRAN
ELSE ROLLBACK TRAN
RETURN @li_RetCode
I have yet to examine the impact of using the SET XACT_ABORT ON.
And regarding the GOTO statement, yes it did allow sloppy (unreadable) coding (aka "spaghetti code") so it became in fashion to denounce it outright. Even though there are legitimate uses for the GOTO statement (for instance terminating a recursive function.
Just don't get me started on the CONCEPT DEMONSTRATION of calculating factorials by recursion when IN PRACTICAL USE this is the worst use of ressources I can think of, especially when the beginning user is not warned about this. (Yes there are legitimate uses for recursion, but factorials an Fibonacci suites are NOT such cases -- a simple loop and a temporary local variable are sufficient.)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply