Is it possible to trap or log FATAL ERROR from a stored proc?
-
Okay now it's starting to make sense!
A bit of clarification on my part first. My code was not throwing the error when the sp_executesql call ran. This ran, but accidentally deleted a table that I didn't want to delete. Then a subsequent step in the proc code attempted to do an update of that table, which was mistakenly dropped. The subsequent step was a straight sql update statement (no dynamic sql). This threw a hard error and caused the proc to terminate without rolling back the open transaction.
Now in looking at Philip's code snippet above, I decided to try the following:
declare @sql_string nvarchar(255)
declare @returncode int
select @sql_string = 'update pretend_table set object_status = 1'
begin tran
exec @returnCode = sp_executesql @sql_string
if @@error <> 0 or @returncode <> 0
begin
select @@error, @returncode
select 'Hey! No such table!'
rollback
goto proc_finish
end
commit
proc_finish:
select @@trancount
I execute the above code and here's what I got:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'pretend_table'.
0 208
(1 row(s) affected)
Hey! No such table!
(1 row(s) affected)
0
(1 row(s) affected)
Note the following:
- The code continues to run even though the call to sp_executesql caused a fatal error to occur.
- The @@error value is indeed zero, just as Philip found
- The @returncode value however is 208, indicating that sp_executesql returns the value of @@error generated when it runs as the return value.
Philip's code above nested the call to sp_executesql inside another stored proc (philproc). So what happens is, philproc runs, calls sp_executesql. Sp_executesql returns 208 as its return code, but returns @@error = 0 even though a fatal error occurred when sp_executesql ran. The remainder of the proc attempts to trap an error by looking at the value of @error. But @error never changed from zero. By examining @ret_code for a non-zero value after the call to sp_executesql, one can trap a fatal error that occurs inside the call to sp_executesql. If philproc were modified to return @ret_code as it's return value, rather than @error, you should get back 208 rather than 0.
-
Phil has invited me in, here's my tuppence worth.
I altered the SP philproc to use Dynamic SQL and called it two different ways. I then altered it to use a 'straight' select & called it two different ways. Check out the results...
Dave J
Set NoCount On
GO
ALTER PROCEDURE philproc
as
declare @sql nvarchar(2000)
declare @error int, @ret_code int
set @sql = 'select * from fred'
exec @ret_code = sp_executesql @sql -- Induces an untrappable error (208)
-- as fred does not exist
-- BUT Execution flow does NOT return to the calling code
SELECT @error = @@error -- this line is executed, and @error is left 0
PRINT 'Point 1' -- this line is executed
Select @error as [Error in SP]
IF @error <> 0 -- These lines are executed; RETURN would be 0, but is set to 208
RETURN @error
ELSE
RETURN 0
go
declare @sql_string nvarchar(255)
declare @returncode int
select @sql_string = 'exec philproc'
begin tran
exec @returnCode = sp_executesql @sql_string
if @@error <> 0 or @returncode <> 0 --Error and Return Code is Zero!
begin
select @@error as [Error], @returncode as [Return Code]
select 'Hey! No such table!'
rollback
goto proc_finish
end
--commit --commented out deliberately
proc_finish:
select 'Trancount is ' + cast(@@trancount as varchar(10))
GO
declare @returncode int
begin tran
exec @returnCode = philproc
if @@error <> 0 or @returncode <> 0
begin --this works as no sp_ececuteSQL to get in the way.
select @@error as [Error], @returncode as [Return Code]
select 'Hey! There really is No such table!' --falls into here as return code is 208
rollback
goto proc_finish2
end
--commit --commented out deliberately
proc_finish2:
select 'Trancount still is ' + cast(@@trancount as varchar(10))
GO
ALTER PROCEDURE philproc
as
declare @sql nvarchar(2000)
declare @error int, @ret_code int
select * from fred --non dynamic version, will STOP here
-- Induces an untrappable error (208)
-- as fred does not exist
-- Execution flow returns to the calling code
SELECT @error = @@error -- this line is never executed, and @error is left undefined
PRINT 'Point 3' -- this line is never executed
Select @error as [Error in SP]
IF @error <> 0 -- These lines are never executed; RETURN status is @@Error
RETURN @error
ELSE
RETURN 0
GO
declare @sql_string nvarchar(255)
declare @returncode int
select @sql_string = 'exec philproc'
begin tran
exec @returnCode = sp_executesql @sql_string
if @@error <> 0 or @returncode <> 0 --Error is 208 and Return Code 0!
begin
select @@error as [Error], @returncode as [Return Code]
select 'Hey! No such table!'
rollback
goto proc_finish3
end
--commit --commented out deliberately
proc_finish3:
select 'Trancount is ' + cast(@@trancount as varchar(10))
GO
declare @returncode int
begin tran
exec @returnCode = philproc
if @@error <> 0 or @returncode <> 0
begin
select @@error as [Error], @returncode as [Return Code] --Error is 0 and Retrun Code is NULL
select 'Hey! There really, really, REALLY is No such table!' --falls into here as return code is NULL
rollback
goto proc_finish4
end
--commit --commented out deliberately
proc_finish4:
select 'Trancount still is ' + cast(@@trancount as varchar(10))
-
I think we may all have missed an obvious point here (or perhaps it's only just dawned on me?)
When calling the dynamic SQL using sp_executesql, we've just called a nested stored procedure. In other words, we've transferred the site of the fatal error from our own stored proc to another one. In my example, I've not been testing for the return status of sp_executesql in the same way as for other stored procs.
My standard way of doing that for ANY SP would usually be the same as Dave's (since we developed the standards together in any case), and would be as follows:
declare @error_stat int, @return_stat int
exec @return_stat = philproc
set @error_stat = @@error-- Non-zero if untrappable error occurred in SP
if @return_stat 0 or @error_stat 0
begin
if @@trancount 0-- Rollback any open transaction
rollback tran
if @error_stat 0-- Return any untrappable error code …
return @error_stat
else-- Return any trappable error code …
return @return_stat
end
(Apologies for the rubbish formatting, but my browser won't let me use rich text features on this site)
In all our examples, if we'd used the same approach when calling sp_executesql, we'd have been OK. I confess my own examples weren't quite that rigorous either.
So, philproc should now look like:
alter procedure philproc
as
declare @error int, @ret_code int
declare @sql nvarchar(2000)
begin tran
set @sql = 'select * from #fred'
exec @ret_code = sp_executesql @sql
select @error = @@error
if @error 0 or @ret_code 0
begin
if @@TRANCOUNT > 0
rollback
if @error 0
return @error
else
return @ret_code
end
else
begin
commit
return 0
end
go
It now won't matter whether philproc uses dynamic or straight SQL - as long as we call philproc correctly, too, we should trap all the errors.
-
"I think we may all have missed an obvious point here (or perhaps it's only just dawned on me?)"
That's the point I was trying to make, 
it's the sp_executeSQL call that is getting in the way. Too subtle? Moi? 
Dave
PS How does the quote button (In this forum) work? It appears to do nothing for me...
Oh hang on, it quoted the first few lines...ignore question above!
-
Yes, Dave - I'm not used to you being subtle 🙂 When did this start?
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply