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.

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:

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:

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:

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:

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:

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:

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:

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:

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: