The point was raised recently at work, about what exception handling should be done
A stored procedure was attempting an operation and a number of undesirable outcomes could arise. THese were signalled by the proc exiting normally but setting an out parameter to a numeric error code.
I pointed out that this wasnt sensible; each operation as a function should logically only have one successful outcome, with anything else being a fail. If AddTransactionToAccount failed because the account was closed, there were no cards, there wasnt enough funds etc.. then the sproc shouldnt exit normally but set an error code.. it should raise a custom exception with a relevant code and message.
The crazy thing was, the client side code would look like:
Try
cmd.ExecuteNonQuery()
If outParam.Equals("000") Then return 'success
Else If outParam.Equals("001") Then NotEnoughFunds()
..
Catch OracleException
..handle errors here like database disconencted etc
If ex.Message.Contains ...
End Try
There are problems with such an approach:
You cant bubble exceptions without checking a code and throwing
It doesnt make sense to have 2 blocks of code for handling errors
It doesnt make sense to waste extra CPU cycles on always running a set of IF after every call to the sproc
The big issue was that this sproc was returning a cursor that code written by Microsoft was using to fill a datatable.. Now, because MS' code would assume the cursor was ready if the sproc completed, we were getting errors because the sproc was completing normally even though errors had occurred. I just could not get this concept through to the guys programming this:
Exceptions exist for a reason and should be thrown as far as possible. It is not down to the database to make decisions how to handle an exception and then convert it into an error code to pass back to the UI. The established, understood, assumed method of reporting exceptional circumstances is to throw an exception, NOT catch the exception before execution leaves the database, and convert it into an out param string indicating something went wrong..
