Take the 2-minute tour ×

We implement the majority of our business rules in the database, using stored procs.

I can never decide how best to pass data constraint violation errors from the database back to the user interface. The constraints I'm talking about are tied more to business rules than data integrity.

For example, a db error such as "Cannot insert duplicate key row" is the same as the business rule "you can't have more than one Foo with the same name". But we've "implemented" it at the most common sense location: as a unique constraint that throws an exception when the rule is violated.

Other rules such as "You're only allowed 100 Foos per day" do not cause errors per-say, since they're gracefully handled by custom code such as return empty dataset that the application code checks for and passes back to the ui layer.

And therein lies the rub. Our ui code looks like this (this is AJAX.NET webservices code, but any ajax framework will do):

WebService.AddFoo("foo", onComplete, onError); // ajax call to web service

function onComplete(newFooId) {
    if(!newFooId) {
        alert('You reached your max number of Foos for the day')
    // update ui as normal here

function onError(e) {
    if(e.get_message().indexOf('duplicate key')) {
        alert('A Foo with that name already exists');
    // REAL error handling code here

(As a side note: I notice this is what stackoverflow does when you submit comments too quickly: the server generates a HTTP 500 response and the ui catches it.)

So you see, we are handling business rule violations in two places here, one of which (ie the unique constaint error) is being handled as a special case to the code that is supposed to handle real errors (not business rule violations), since .NET propagates Exceptions all the way up to the onError() handler.

This feels wrong. My options I think are:

  1. catch the 'duplicate key violation' exception at the app server level and convert it to whatever it is the ui expects as the "business rule violated" flag,
  2. preempt the error (say, with a "select name from Foo where name = @Name") and return whatever it is the app server expects as the "business rule violated" flag,
  3. in the same ballpark as 2): leverage the unique constraint built into the db layer and blindly insert into Foo, catching any exceptions and convert it to whatever it is the app server expects as the "business rule violated" flag
  4. blindly insert into Foo (like 3) and let that Exception propagate to the ui, plus have the app server raise business rule violations as real Exceptions (as opposed to 1). This way ALL errors are handled in the ui layer's onError() (or similar) code.

What I like about 2) and 3) is that the business rule violations are "thrown" where they are implemented: in the stored proc. What I don't like about 1) and 3) is I think they involve stupid checks like "if error.IndexOf('duplicate key')", just like what is in the ui layer currently.

Edit: I like 4), but most people say to use Exceptions only in exceptional circumstances.

So, how do you people handle propagating business rule violations up to the ui elegantly?

share|improve this question

6 Answers 6

up vote 1 down vote accepted

A stored procedure may use the RAISERROR statement to return error information to the caller. This can be used in a way that permits the user interface to decide how the error will appear, while permitting the stored procedure to provide the details of the error.

RAISERROR can be called with a *msg_id*, severity and state, and with a set of error arguments. When used this way, a message with the given *msg_id* must have been entered into the database using the sp_addmessage system stored procedure. This *msg_id* can be retrieved as the ErrorNumber property in the SqlException that will be raised in the .NET code calling the stored procedure. The user interface can then decide on what sort of message or other indication to display.

The error arguments are substituted into the resulting error message similarly to how the printf statement works in C. However, if you want to just pass the arguments back to the UI so that the UI can decide how to use them, simply make the error messages have no text, just placeholders for the arguments. One message might be '"%s"|%d' to pass back a string argument (in quotes), and a numeric argument. The .NET code could split these apart and use them in the user interface however you like.

RAISERROR can also be used in a TRY CATCH block in the stored procedure. That would allow you to catch the duplicate key error, and replace it with your own error number that means "duplicate key on insert" to your code, and it can include the actual key value(s). Your UI could use this to display "Order number already exists", where "x" was the key value supplied.

share|improve this answer
I wish the person who downvoted this and stackoverflow.com/questions/581994/… would give a reason. Two very different answers, same result, within a minute. –  John Saunders Apr 5 '09 at 16:47
The code that eventually calls RAISERROR will still need to perform a "stupid check" like CHARINDEX('duplicate key', @errorMessage) > 0 and I found this question because my example scenario involves two potential unique key constraint violations and I was curious whether there was some way to determine which key has been violated without searching for either the names of the keys or the names of the relevant columns. –  Kenny Evitt Jul 27 '11 at 19:07

We don't perform our business logic in the database but we do have all of our validation server-side, with low-level DB CRUD operations separated from higher level business logic and controller code.

What we try to do internally is pass around a validation object with functions like Validation.addError(message,[fieldname]). The various application layers append their validation results on this object and then we call Validation.toJson() to produce a result that looks like this:

    general_message:"You have reached your max number of Foos for the day",
        last_name:"This field is required",
        mrn:"Either SSN or MRN must be entered",
        zipcode:"996852 is not in Bernalillo county. Only Bernalillo residents are eligible"

This can easily be processed client side to display messages related to individual fields as well as general messages.

Regarding constraint violations we use #2, i.e. we check for potential violations before insert/update and append the error to the validation object.

share|improve this answer
I notice this is what SO does for write operations like voting. The response has "Success" and "Message" fields. But if you submit comments too quickly they throw a 500 server exception and the ui catches it (ie what my code example does, which I don't like). –  Crescent Fresh Feb 22 '09 at 16:32
I do like this approach though, and it wouldn't be impossible to do even with business rule checks all handled in the db. The app server just has to translate all responses to a standard that the ui can handle. Which isn't a bad thing to do regardless. –  Crescent Fresh Feb 22 '09 at 16:38

The problem is really one of a limitation in the architecture of your system. By pushing all logic into the database, you need to handle it in two places (as opposed to building a layer of business logic that links the UI with the database. Then again, the minute you have a layer of business logic you lose all the benefits of having logic in stored procs. Not advocating one or the other. The both suck about equally. Or don't suck. Depending on how you look at it.

Where was I? Right.

I think a combination of 2 and 3 is probably the way to go.

By pre-empting the error you can create a set of procedures that can be called from the UI-facing code to provide detailed implementation-specific feedback to the user. You don't necessarily need to do this with ajax on a field-by-field basis, but you could.

The unique constraints and other rules that are in the database then become the final sanity-check for all data, and can assume that data is good before being sent, and throw Exceptions as a matter of course (the premise being that these procedures should always be called with valid data and therefor invalid data is an Exceptional circumstance).

share|improve this answer
That's actually a combination of 2 and 4, with the tweak to 4 that instead of raising errors at the app server they are raised in the stored proc. So the stored proc throws exceptions for ALL business rule violations (some it caught itself, some the db caught for it). I like. –  Crescent Fresh Feb 22 '09 at 13:13
You're right ... –  Toby Hede Feb 23 '09 at 1:47

In defense of #4, SQL Server has a pretty orderly hierarchy of error severity levels predefined. Since as you point out it's well to handle errors where the logic is, I'd be inclined to handle this by convention between the SP and the UI abstraction, rather than adding a bunch of extra coupling. Especially since you can raise errors with both a value and a string.

share|improve this answer

I've seen lots of Ajax based applications doing a real-time check on fields such as username (to see if it already exists) as soon as the user leaves the edit box. It seems to me a better approach than leaving to the database to raise an exception based on a db constraint - it is more proactive since you have a real process: get the value, check to see if it is valid, show error if not, allow to continue if no error. So it seems option 2 is a good one.

share|improve this answer
That's nice for the UX, but a big race condition is there that the ui must still deal with on submission of the data. –  Crescent Fresh Feb 22 '09 at 3:38
We get around the race condition this by doing a full server-side validation on submit. The per-field validation is just a convenience for the user –  Mark Porter Feb 22 '09 at 15:53
@Mark: for sure. I'm just wondering how people handle the "full server-side validation" in the ui (assuming ajax to submit the data) when a violation does occur - which you answered in your other answer, thank you. –  Crescent Fresh Feb 22 '09 at 16:42

This is how I do things, though it may not be best for you:

I generally go for the pre-emptive model, though it depends a lot on your application architecture.

For me (in my environment) it makes sense to check for most errors in the middle (business objects) tier. This is where all the other business-specific logic takes place, so I try to keep as much of the rest of my logic here too. I think of the database as somewhere to persist my objects.

When it comes to validation, the easiest errors can be trapped in javascript (formatting, field lengths, etc.), though of course you never assume that those error checks took place. Those errors also get checked in the safer, more controlled world of server-side code.

Business rules (such as "you can only have so many foos per day") get checked in the server-side code, in the business object layer.

Only data rules get checked in the database (referential integrity, unique field constraints, etc.). We pre-empt checking all of these in the middle tier too, to avoid hitting the database unnecessarily.

Thus my database only protects itself against the simple, data-centric rules that it's well equipped to handle; the more variable, business-oriented rules live in the land of objects, rather than the land of records.

share|improve this answer
Nice. Curious though: what do you do about the race condition where a check in the middle tier passes, but fails at the data tier (due to say, another thread beating it to the punch)? –  Crescent Fresh Feb 22 '09 at 23:30
Yeah, that's totally the weakness in this approach. In my particular environment, this is such a rare case that we don't need to worry about it. It would result in a rather ugly exception, true, but the database would remain intact. –  teedyay Feb 23 '09 at 6:16

Your Answer


