SQL Server error handling: exceptions and the database-client contract

Question

We’re a team of SQL Servers database developers. Our clients are a mixed bag of C#/ASP.NET, C# and Java web services, Java/Unix services and some Excel.

Our client developers only use stored procedures that we provide and we expect that (where sensible, of course) they treat them like web service methods.

Some our client developers don’t like SQL exceptions. They understand them in their languages but they don’t appreciate that the SQL is limited in how we can communicate issues.

I don’t just mean SQL errors, such as trying to insert “bob” into a int column.

I also mean exceptions such as telling them that a reference value is wrong, or that data has already changed, or they can’t do this because his aggregate is not zero.

They’d don’t really have any concrete alternatives: they’ve mentioned that we should output parameters, but we assume an exception means “processing stopped/rolled back.

How do folks here handle the database-client contract? Either generally or where there is separation between the DB and client code monkeys.

Edits:

• we use SQL Server 2005 TRY/CATCH exclusively
• we log all errors after the rollback to an exception table already
• we're concerned that some of our clients won't check output paramaters and assume everything is OK. We need errors flagged up for support to look at.
• everything is an exception... the clients are expected to do some message parsing to separate information vs errors. To separate our exceptions from DB engine and calling errors, they should use the error number (ours are all 50,000 of course)

Answer 1

I generally use an output parameter - and define the possible values.

0 = success
Postive Integer (on an insert let's say) = New Row Id (@@identity)

Negative Integer = Known Possible Error Conditions
-1 = Missing @LastName (or zero length)
-2 = Missing @FirstName (or zero length)
...etc...

This becomes a bit tedious to define - but it can be extensible - and it is a way to get meaningful results back to your clients and for a data access layer to pass back to a business objects layer an exact reason for failure (I use enums in the Business Objects layer for different status conditions).

It is also possible for a Data Access Layer to throw exceptions if desired - but I believe cost-wise from a performance perspective you are better off not throwing errors when you could just check an enum or integer value.

There are other techniques - this is just one that I have used with some measure of success in the past.

Answer 2

here is my recommendation:

return 0 when everything is ok
return negative x when a logical error hapens, missing or invalid data
return positive x when a fatal error happens, insert failure, etc.

add output parameters ErrorMsg and ErrorLog onto the stored procedures. ErrorMessage will contain a human readable message, ErrorLog will contain debug info.
These will be NULL if return 0
You can use ErrorLog to record any problems, make sure if you are inserting them into a table, you do it after the rollback.

use TRY - Catch to capture problems, and build your own message, and return the info using the above conventions.

Answer 3

At my current job, we reserve exceptions for exceptional conditions. For things like bad parameters or the like, we have standard return codes - invalid parameter values always return 98, for example. (why 98? that's lost to history...)

This is not necessarily ideal b/c both the client and the SP have to understand what a particular return code means; it's a bad separation of concerns.

In special cases, we use OUT parameters to return messages, and our batch services use a standardised #scratch table setup.

At a prior employer, we used custom SQL errors for the same purposes. So, use what makes sense. Personally, I prefer a single mechanism but that may not be possible depending on your environment.

Maybe you all ought to get together to develop standardised ways for the client code to handle it. The VBA used in Excel has distinct limitations that the C# and Java code don't, and as similar as Java & C# are to each other, they're not identical. If you're going to stick with returning exceptions, then if you can agree on a) a known message # range that indicate "error" exceptions and b) a standardised message layout so that standardised parsing can occur, the developers would be 90% done.

Answer 4

We regard a stored proc call as a method so we use exception-driven messages, just like you would in a client language.

Anything else seems just too complex...

Thank you for your answers.