SQL Server Error Handling: Exceptions and Database Client Contract

Question

There was a SQL Server database development team. Our customers are a mixed package of web services C # / ASP.NET, C # and Java, Java / Unix services and some Excel.

Our client developers use only the stored procedures that we provide, and we expect that (where reasonable, of course) they will consider them as web service methods.

Some of our client developers do not like SQL exceptions. They understand them in their languages, but they do not understand that SQL is limited in how we can deal with problems.

I just don't mean SQL errors, such as trying to insert "bob" in an int column.

I also mean exceptions, such as telling them that the reference value is incorrect or that the data has already been changed or they cannot do it because its totality is not zero.

Theyd really have no specific alternatives: they mentioned that we should output the parameters, but we assume that the exception means "processing stopped / rollback".

How do people here handle the database client contract? Either in general, or where there is a separation between DB monkeys and client code.

Changes:

• we use exclusively SQL Server 2005 TRY / CATCH.
• we register all errors after rolling back to the exception table already
• We are concerned that some of our customers will not check the output parameters and assume that everything is in order. We need bugs marked for support.
• all exception ... clients are expected to do some sort of parsing of messages to separate information against errors. To separate our exceptions from database errors and call errors, they must use the error number (we have all 50,000, of course)

Answer 1

Well, I'm a client code monkey that works a lot with databases. This is how I deal with this.

Exceptions (raiseerrors) that occur in SQL propagate back to the caller. This will include ref constraints, unique index violations, more serious issues, etc. In principle, everything that does not lead to the normal operation of the data should be distributed back.

The C # caller must have the following:

catch (SQLException sqlEx) 

And then handle the exception as needed. They must have a special SQLException handler. It is important.

I generally can not move away from the output parameters, because I believe that they are associated with the transmitted data, and not with error messages, in addition, I can check the exception for the SQL Server error code, so all the data that we need should be that is the exception.

In addition, in some cases with SQL Server, we have stored procedures that can cause a "business-type exception". In these cases, we add an error number (above 50,000) and raise this error in the stored procedure when necessary. In general, we try to minimize them, because it adds complexity, but in some cases we find that they are necessary.

Now, since the client is catching a SQLException, they can look at the error code returned by SQL Server in the exception, and then take some special action (if necessary) when the exception is caught and the error number is a specific value. This allows you to use a secondary level of error handling based on the error code, if necessary for user errors (> 50,000).

It also allows database administrators to raise user errors and have a consistent way of interacting with client code. Database administrators then had to tell the monkey client code what user errors were such that they could prepare for them.

I usually don’t use return codes to handle errors, although I can see how they can be used, but this means that in the code monkey layer you need more logic to look at and process the return code. If this is a problem, I want an exception, because then I can deal with them sequentially. If I need to also look at return codes, now there are several ways to handle errors.

Answer 2

I usually use the output parameter - and determine the possible values.

0 = success
Postive Integer (insert, say) = New Row Id (@@ identity)

Negative integer = known possible error conditions
-1 = Missing @LastName (or zero length)
-2 = Missing @FirstName (or zero length)
... etc...

It gets a little tedious to define - but it can be extensible - and it's a way to get meaningful results for your customers and for the data access layer, to pass back the exact reason for the failure back to the business object level (I use enumerations at the Business Objects level for different state states).

It is also possible that the data access level may throw exceptions if necessary, but I believe that from a performance point of view, you better not throw errors if you can just check the enum value or integer.

There are other methods - this is only the one that I have used with some success in the past.

Answer 3

here is my recommendation:

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

Add the output parameters ErrorMsg and ErrorLog to the stored procedures. ErrorMessage will contain a human read message, ErrorLog will contain debugging information.
They will be NULL if return 0
You can use ErrorLog to record any problems, make sure you insert them into the table, you do it after the rollback.

use TRY - Catch to capture problems and create your own message and return information using the above conventions.

Answer 4

In my current work, we reserve exceptional conditions for exceptions. For things like bad parameters, etc., We have standard return codes - incorrect parameter values ​​always return 98, for example. (why 98? who lost stories ...)

This is not necessarily an ideal b / c, and the client and SP should understand what a specific return code means; this is a poor separation of concerns.

In special cases, we use the OUT parameters to return messages, and our batch services use the standard #scratch table setting.

In the previous employer, we used custom SQL errors for the same purpose. So use what makes sense. Personally, I prefer one mechanism, but it may not be possible depending on your environment.

Perhaps you should all come together to develop standardized ways to handle client code. The VBA used in Excel has various limitations that do not have C # and Java code, and similar to Java and C #, they are not identical. If you intend to stick to the returned exceptions , then if you can agree on a) a known message range # that indicates “error” exceptions, and b) a standardized message layout so that parsing can be standardized, the developers would do 90%.