Error hanlding in Stored procedure or .Net Application   

Dear Professional,

I had serious debate in my organization and I told to my developers to use 
Error Handling in Stored procedure BEGIN TRY END TRY BEGIN CATCH END CATCH 
but they are saying error handling is better in .Net code instead of stored 
procedure,

Please advice me which way is better and give me any solid reason so that I 
can explain them very easily.

Your help is really appreciated 

Thanks in advance.

date: Fri, 4 Jul 2008 20:01:01 -0700   author:   Rogers

RE: Error hanlding in Stored procedure or .Net Application   

For some, this can be a quite emotional topic. But in general, the software 
engineering principles apply here. That is, you want to maintain modular 
code, you want to keep encapsulation, and you want to ensure tight cohesion 
inside a module and loose coupling between modules. So the key argument 
against handling all the errors in the application code (.NET C# code for 
instance) is that it destroys encapsulation, and makes the error handling 
logic unnecessarily complex in the app when it can be simpler if certain 
errors are handled inside T-SQL. Especially, for some proc errors, if all 
that the app should do is to just resubmit the proc, handling the errors in 
the proc can save you some network trips. 

But on the other hand, you can't really dictate that all the errors should 
be handle inside T-SQL. First of all, the T-SQL TRY/CATCH mechanism is not 
designed to catch all the errors. So it can't all be done all inside T-SQL. 
Secondly, if the logical action of a proc error is to do something else 
beyond that proc, it's probably better to let the app control the logic.

So unfortunately there is no simple answer, although there are some general 
rules of thumb (such as you want to contain the error handling to the 
smallest scope) as the derivatives of general software engineering 
principles. You really have to examine each error condition on a case by case 
basis.

Linchi

"Rogers" wrote:

> Dear Professional,
> 
> I had serious debate in my organization and I told to my developers to use 
> Error Handling in Stored procedure BEGIN TRY END TRY BEGIN CATCH END CATCH 
> but they are saying error handling is better in .Net code instead of stored 
> procedure,
> 
> Please advice me which way is better and give me any solid reason so that I 
> can explain them very easily.
> 
> Your help is really appreciated 
> 
> Thanks in advance.

date: Sun, 6 Jul 2008 20:06:00 -0700   author:   Linchi Shea

RE: Error hanlding in Stored procedure or .Net Application   

By the way, Adam Machanic has a chapter on errors and exceptions handling in 
his book "Expert SQL Server 2005 Development". You may want to take a read. 
Also, he did a presentation on the same topic at this year's TechEd, but I'm 
not sure if the presentation can be found online somewhere.

Linchi

"Rogers" wrote:

> Dear Professional,
> 
> I had serious debate in my organization and I told to my developers to use 
> Error Handling in Stored procedure BEGIN TRY END TRY BEGIN CATCH END CATCH 
> but they are saying error handling is better in .Net code instead of stored 
> procedure,
> 
> Please advice me which way is better and give me any solid reason so that I 
> can explain them very easily.
> 
> Your help is really appreciated 
> 
> Thanks in advance.

date: Mon, 7 Jul 2008 12:40:01 -0700   author:   Linchi Shea

Re: Error hanlding in Stored procedure or .Net Application   

Thanks for your reply.



"Linchi Shea"  wrote in message 
news:526396DF-41B1-4233-8EC5-77CB7ED6A32B@microsoft.com...
> By the way, Adam Machanic has a chapter on errors and exceptions handling 
> in
> his book "Expert SQL Server 2005 Development". You may want to take a 
> read.
> Also, he did a presentation on the same topic at this year's TechEd, but 
> I'm
> not sure if the presentation can be found online somewhere.
>
> Linchi
>
> "Rogers" wrote:
>
>> Dear Professional,
>>
>> I had serious debate in my organization and I told to my developers to 
>> use
>> Error Handling in Stored procedure BEGIN TRY END TRY BEGIN CATCH END 
>> CATCH
>> but they are saying error handling is better in .Net code instead of 
>> stored
>> procedure,
>>
>> Please advice me which way is better and give me any solid reason so that 
>> I
>> can explain them very easily.
>>
>> Your help is really appreciated
>>
>> Thanks in advance.

date: Sat, 19 Jul 2008 10:20:45 -0400   author:   Noor