How to NOT trap errors in a stored procedure
By Chris Miller
on 01 December 2001
| 13 Comments
| Tags: Stored Procedures
I was trying to track down an error in one of our internal OLAP collection processes, when I noticed something very peculiar: An insert was failing, appeared to have the proper error trapping, but was not causing an error. How does that work?
Let's say you've got a batch that looks like this:
declare @err int,
@RC int
set @err = 0
set @rc = 0
insert into BigTable
select * from ImportTable
set @RC = @@ROWCOUNT
set @err = @@ERROR
if @@ERROR <> 0
begin
rollback transaction
print 'Transaction Failed'
end
So, what is the value of @err if the INSERT fails? It's the same as the value if the INSERT works. @@ERROR and @@ROWCOUNT are only valid for the statement which executed immediately previous to the use of the variable. So, the @@ERROR was quite unhelpfully trapping any errors which resulted from the assignment of @@ROWCOUNT to @RC. Not exactly as intended. The correct way of writing it is to use SELECT, like this:
select @RC = @@ROWCOUNT, @err = @@ERROR
-rs
