Error handling in called stored procedures

[balushanmugham]

Hi,

I have one main stored procedure. It calls other 10 stored procedures by giving input parameters.
Do I want to implement Begin Try/Begin catch in each sub procedures or in main proceudre only.

Please suggest how to achieve?
Appreciate your help.

Regards,
Bala

 

[Olaf Doschke]

TRY...CATCH is structured error handling and is there to catch errors where they occur and perhaps handle them automatically.
So yes, you'll do error handling in each procedure, when you can do something with some exceptions, even if those procedures are only internal.
If there is nothing you can do about any specific exception you delegate it's handling to the next higher level, bubbling up exceptions.
Unfortunately you can only do so by RAISEERROR and that won't allow to reraise the original error.

So anyway, TRY CATCH only is valuable for cases you can catch an error be resolving it. If you have errors in a transaction your transaction typically get doomed and you have to roll back, then this handling doesn't help much besides you can do an automatic roll-back. Problem is the outside client won't get noticed about the failure of the call.

Bye, Olaf.