tsql - 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

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.