Error Handling - Efficient way
-
Hi All,
We are coming up with a OLTP kin of application. To start with we are little concerned upon how to handle errors (hope in the next version sql server will come with exceptions in line with oracle).
Two ways we thought of is
1. After every dml statement check for errornum and have a GOTO section where we handle all the errors.
2. After every dml have an insert statement with values errornum and description. Check this table after a complete logical step.
Can any of you provide with a better alternative.
Thanks & Regards,
Mitra
-
-
I use #1, but like this:
declare @err int
select @err = 0
-- do something
insert.....
if @@error <> 0
select @err = -1
-- do something else
update.....
if @@error <> 0
select @err = -1
--repeat
-- check
if @err <> 0
-- process error
I use the var as a flag and rollback transactions at the end.
There are times, however, where I do not want the entire transaction tried, in this case, check @err before every step and do not proceed if it is flagged.
update
Steve Jones
-
Don't like that as it means causes problems if someone accidentally clears the flag.
Prefer
declare @error int, @rowcount int
statement
select @error = @@error, @rowcount = @rowcount
if @error <> 0
goto ErrHnd
begin tran
statement
select @error = @@error, @rowcount = @rowcount
if @error <> 0
goto TxnErrHnd
commit tran
return
ErrHnd:
raiserror('failed code = %d', 16, -1, @error
return
TxnErrHnd:
raiserror('failed code = %d', 16, -1, @error
rollback tran
return
Could also set a message before the goto to give the statement that fails.
Cursors never.
DTS - only when needed and never to control. -
Flag should never be cleared. I control the source, so this as good as it gets. If the flag gets cleared by someone, they'll be explaining to the CTO and potentially looking for another gig!
Steve Jones
-
What happens when you leave (or take a holiday).
I just like to try and make it easier for people.
Had someone recently
if @error = 0
begin
stmnt
select @error = @@error
end
if @error = 0
stmnt
select @error = @@error
end
which not only made the code unreadable but also meant that the flag got cleared by someone (actually he himself) checking another flag with an 'or' in the same test.
Cursors never.
DTS - only when needed and never to control. -
what a knucklehead. I'd say grasshopper still needs to practice grabbing the stone from your hand
When I go on vacation I dread coming back to fix the problems like this. Oh well, keeps me employed and gives me a chance to teach someone something.
Steve Jones
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply