Problem Using Try catch...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ash - 24 Jul 2008 05:18 GMT

Since now I'am trapping my stored procedure errors at my Catch block in .Net
code. But my DBA now has insisted to use Try Catch blocks in stored
procedures itself...

As you would expect the exception is now not raised to .Net as it is handled
by the SP itself, but I need to re-raise the error to my .Net code...

I'm helpless despite using the RAISERROR method(below)... it doesn't signal
my .Net code the error...

begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_number()
,@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

Pls. check have I missed something, or suggest any alternate to re-raise the
error to the .Net code with ignoring the Try Catch block in my Stored
procedure.

thanks...

Reply to this Message

Andrew J. Kelly - 24 Jul 2008 19:59 GMT

You are setting the ErrorSeverity variable to the Error_Number() and then
using it as the severity parameter in the Raiserror. The parameter should
be a number from 1 to 16 generally and that determines how the batch, sp
etc. treats it. If the severity used in the Raiserror is not high enough it
will not be passed back. If it is too high it kills the batch. I suggest
you read the remarks in BooksOnLine for RaiseError that talks about the
severity and TRY CATCH. I also suggest reading this as well:
http://www.sommarskog.se/error-handling-II.html

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Since now I'am trapping my stored procedure errors at my Catch block in
> .Net
[quoted text clipped - 21 lines]
>
> thanks...

Reply to this Message