October 20, 2009 at 2:21 am
Hi all,
I know there's probably a really weird and wonderful technical explanation for this but I can't get my head around why error trapping works differently depending on what type of error is encountered..
It's hard to explain so I'll throw the code at you instead.
This example forces a divide by zero error.
create procedure sptest_inner
as
declare @varLogMessage varchar(max)
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
select 'Inner SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 2)
END CATCH
go
create procedure sptest_outer
as
declare @varLogMessage varchar(max)
BEGIN TRY
exec sptest_inner
END TRY
BEGIN CATCH
select 'Outer SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 1)
END CATCH
go
exec sptest_outer
go
drop procedure sptest_inner
drop procedure sptest_outer
go
As expected, both layers trap the error (due to RAISERROR)
-----------
(0 row(s) affected)
--------
Inner SP
(1 row(s) affected)
--------
Outer SP
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure sptest_outer, Line 12
Divide by zero error encountered.
This example forces a missing object error.
create procedure sptest_inner
as
declare @varLogMessage varchar(max)
BEGIN TRY
IFNOT EXISTS(SELECT TOP 1 NULL FROM dbo.IDONTEXIST WITH (NOLOCK))
BEGIN
select 'hello!'
END
END TRY
BEGIN CATCH
select 'Inner SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 2)
END CATCH
go
create procedure sptest_outer
as
declare @varLogMessage varchar(max)
BEGIN TRY
exec sptest_inner
END TRY
BEGIN CATCH
select 'Outer SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 1)
END CATCH
go
exec sptest_outer
go
drop procedure sptest_inner
drop procedure sptest_outer
go
Unlike the previous error, this one ignores the error trapping in the inner SP!
--------
Outer SP
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure sptest_outer, Line 12
Invalid object name 'dbo.IDONTEXIST'.
Why is this? And how do I prevent it? I want the inner SP to trap any kind of error, as in the real world this nested SP has some tidying up to do when there is any kind of error.
October 20, 2009 at 3:12 am
See BOL Here
http://msdn.microsoft.com/en-us/library/ms175976.aspx
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
* Compile errors, such as syntax errors, that prevent a batch from running.
* Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
Your only option is to test @@Error after the exec.
October 20, 2009 at 3:27 am
I'm already capturing the error in the outer SP (even though I don't want to), so checking @@Error after the EXEC will be pretty much the same won't it?
The inner SP must do the tidying up, on any kind of error.
I'm guessing if SQL is unable to trap these kinds of errors then TRY..CATCH isn't as robust as it should be and we need to use a belt and bracers approach, e.g. check all tables exist at the top or something. Messy.. :/
October 20, 2009 at 3:42 am
janine.rawnsley (10/20/2009)
I'm already capturing the error in the outer SP (even though I don't want to), so checking @@Error after the EXEC will be pretty much the same won't it?The inner SP must do the tidying up, on any kind of error.
I'm guessing if SQL is unable to trap these kinds of errors then TRY..CATCH isn't as robust as it should be and we need to use a belt and bracers approach, e.g. check all tables exist at the top or something. Messy.. :/
The point is that innersp has not even executed because of the non existent table.
You can vote on connect here to enhance TRY / CATCH.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=496758
Try this will correctly handle the error
create procedure sptest_outer
as
declare @varLogMessage varchar(max)
BEGIN TRY
exec sptest_inner
if(@@ERROR<>0) begin
-- Do Something
rollback
return
end
END TRY
BEGIN CATCH
select 'Outer SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 1)
END CATCH
go
October 20, 2009 at 3:50 am
That would take us to the CATCH statement rather than the code in your IF block.
(tested it just to be sure)
Also, it DOES execute the inner SP hence my conundrum. Try this to see..
create procedure sptest_inner
as
declare @varLogMessage varchar(max)
BEGIN TRY
select 'Inner SP ENTRY'
IF NOT EXISTS(SELECT TOP 1 NULL FROM dbo.IDONTEXIST WITH (NOLOCK))
BEGIN
select 'hello!'
END
END TRY
BEGIN CATCH
select 'Inner SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 2)
END CATCH
go
create procedure sptest_outer
as
declare @varLogMessage varchar(max)
BEGIN TRY
exec sptest_inner
END TRY
BEGIN CATCH
select 'Outer SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 1)
END CATCH
go
exec sptest_outer
go
drop procedure sptest_inner
drop procedure sptest_outer
go
There is code at the start of my inner sp which is running fine, thus putting the job into an "in progress" state, and code in an error trap to tidy things up / close them off should things so wrong.
The tidy up is never happening because it just bombs out straight to the outer sp, ignoring the error trapping.
I think this is shoddy tbh.. surprised at MS implementing what seems a half-working TRY..CATCH solution.
October 20, 2009 at 3:51 am
/*
The query in SQL server goes through a number of stages like parsing,name resolving,optimazing etc. before executing. Try catch block is meant only for handling the execution errors. In your example the error comes at name resolution. you can handle this situation by wrapping your query in a dynamic sql statement and there by hiding it from the compiler.
*/
Alter procedure sptest_inner
as
declare @varLogMessage varchar(max) ,
@StrSql varchar(1000)
BEGIN TRY
Set @StrSql=
'IF NOT EXISTS(SELECT TOP 1 NULL FROM dbo.IDONTEXIST WITH (NOLOCK))
BEGIN
select ''hello!''
END '
Exec (@StrSql)
END TRY
BEGIN CATCH
select 'Inner SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 2)
END CATCH
go
create procedure sptest_outer
as
declare @varLogMessage varchar(max)
BEGIN TRY
exec sptest_inner
END TRY
BEGIN CATCH
select 'Outer SP'
set @varLogMessage = ERROR_MESSAGE()
RAISERROR(@varLogMessage, 16, 1)
END CATCH
go
exec sptest_outer
go
..
October 20, 2009 at 3:58 am
Thanks Grass.. I know there are a number of ways to "fix" this, one of which would be to check the objects exist and manually raise errors if they don't.
Ultimately though it seems TRY..CATCH doesn't capture every error (at least when using nested SP calls), so I'm wondering if we should be relying on it.
October 20, 2009 at 4:01 am
janine.rawnsley (10/20/2009)
I think this is shoddy tbh.. surprised at MS implementing what seems a half-working TRY..CATCH solution.
Personally , i think its pretty shoddy design that you are trying to execute procedures against table that may or may not exist.
But it is a documented limitation.
October 20, 2009 at 4:07 am
Dave Ballantyne (10/20/2009)
janine.rawnsley (10/20/2009)
I think this is shoddy tbh.. surprised at MS implementing what seems a half-working TRY..CATCH solution.
Personally , i think its pretty shoddy design that you are trying to execute procedures against table that may or may not exist.
But it is a documented limitation.
Using that logic we should all write sp's with code at the top to check every single object which we reference exists before doing anything else. I'd argue any procedure has the issue of running against tables/objects that may or may not exist.
You can't ever guarantee a table will be in place - in this case the table is dropped and created daily so naturally something could go wrong and the table could be missing. As I've already pointed out I can and will check for the object existing now that I'm aware of this limitation.
But it concerns me because there are probably a lot of circumstances in which an object you were expecting may not be there, depending who owns the object, whether it's permanent or "temporary", database schemas and so on. Not to mention some muppet may even drop one of your tables.
So, we are left in a situation where we check every object exists because we are paranoid (according to you our design is shoddy for not doing this), or we assume every object exists and fully rely on error trapping.
Neither sound great to me personally.
October 20, 2009 at 5:02 am
Im going to duck out of this thread , what you are describing is a set of circumstances that are alien to me.
You may as well want a different value for PI.
It really makes no sense that you are 'Paranoid' but at the same time allow 'some muppet' to drop tables.
What if the same muppet done 'Drop database' then what ?
I kind of take your point on temporary tables but not to much,
as a well managed chain of procedures would of initially created the temporary table.
If it couldnt then it would of errored much earlier.
October 20, 2009 at 5:09 am
You're taking my examples a little too literally, they generally don't apply to my world either.
I was simply explaining that there are many circumstances out there, not just for the databases you or I work on, which would result in an object being missing when it was expected.
I wonder who else has been caught out by this, neglected to manually check for missing objects, and wondered why things aren't tidying themselves up when they went to great lengths to add robust error trapping.
You can design things which appear to be rock solid, "there's no way that table could ever be missing!" and still have a table missing because you didn't consider a certain scenario. Is that not why we have error traps in the first place? To capture the things we weren't expecting and try to tidy up / deal with as best as possible?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply