Raising errors in a catch block in SQL2005

[davidz]

When a catchable error occurs in a try block, control is passed to the
associated catch block. While I am then able to examine properties of
the error using such functions as ERROR_NUMBER() in the catch block,
the error-logging scheme used in my company requires that the actual
error be raised so that it can be picked up by components in the next
tier. I appear to be unable to do that -- I can raise some other error
that has all of the properties of the original except the error number.
For example, division by zero raises error 8134. If the code that
produces that error is enclosed in a try block, I seem to be unable to
raise that error from the catch block. Of course, if I never used the
try block to begin with this wouldn't be a problem, but then we
wouldn't have the advantages of this structure as it applies to other
errors, some of which we may wish to handle differently. While
Try-Catch looks great as a way of allowing us to handle errors in a
customized way and to avoid having all errors be passed up to our
middle tier, it seems that we are unable to pass ANY such errors
forward, and that is a problem for us, too. Is my understanding of
this situation correct, or is there some way around this problem, e.g.,
is there any way I could have raised error 8134, in the above example?
Thanks.

[nate.vu]

Hi there,

I believe that you can pass errors forward by including a call to the
RAISERROR routine within your CATCH block.

There is an example on how to do this on this webpage:
http://msdn2.microsoft.com/en-US/library/ms179296.aspx

If you have followed the example on that page, then sorry I couldn't be
of more help

[Erland Sommarskog]

davidz (da****@oz.net) writes:

When a catchable error occurs in a try block, control is passed to the
associated catch block. While I am then able to examine properties of
the error using such functions as ERROR_NUMBER() in the catch block,
the error-logging scheme used in my company requires that the actual
error be raised so that it can be picked up by components in the next
tier. I appear to be unable to do that

Correct. There is no re-raise operation in SQL 2005.

There is a fairly good work around. Just create a custom message, and then
format that message to include all essential information. The middle-tier
components will need to be adapted so if they this particular error number,
for instance 55555, they need to parse the message text to get the
components.

The new error-handling stuff in SQL 2005 is definitely too good to be
ignored!

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx