SQL Server error handling: exceptions and the data

问题:

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)

回答1:

Well I'm a client code monkey that deals with databases a lot. Here's how I handle it.

Exceptions (raiseerrors) that happen in SQL get propagated back to the caller. This would include ref constraints, unique index violations, more serious issues, etc. Basically anything that would not make the data operation occur normally should be propagated back.

The caller C# should have this:

catch (SQLException sqlEx)

And then handle the exception as needed. They should have a specific SQLException handler. This is important.

I generally stay away from output parameters because I consider those to be related to the data being transported and not any error messages, additionally I can inspect the exception for the SQL Server error code so all the data we need should be in that exception.

Additionally, in some cases with SQL Server, we have Stored Procedures that could raise "business type of exceptions". In those cases we add a custom error number (above 50000) and raise that error in the stored procedure when needed. In general we try to keep these at a minimum because it adds complexity, but in some cases, we have found them to be necessary.

Now, since the client is catching the SQLException, they can look at the error code returned by SQL Server in the exception and then take any special action (if needed) when the exception is caught and the error number is a certain value. This allows a secondary level of error handling based on the error code if that is required for the custom errors (>50000).

This also allow the DBAs to raise custom errors and have the client code have a consistent way to deal with them. The DBAs would then have to tell the client code monkey what the custom errors were so they could prepare for them.

I usually don't use the return codes for error handling purposes, although I can see how they could be used, but that means more logic in the code monkey layer to look at and deal with the return code. If they is a problem, I want an exception back, because then I can deal with them consistently. If I have to look at return codes as well, now there a multiple avenues of error handling.

回答2:

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.

回答3:

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.

回答4:

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.