How best pass errors from SQL to business, user tiers?

[Ronald S. Cook]

How would you recommend passing errors back from your proc to your business
tier to the client (assuming you have full control of all layers)?

Take InsertUser for instance. The proc will automatically raise an error if
someone tries to create a user name for themselves that is already taken.

I could relay that error to the client but the message is not so user
friendly.

I could do a SELECT in my proc to see if the user name is already in use and
could then send back @@ERROR = 99 or some other value. But then that would
require my two layers "getting together" on what each value will mean.

Should I instead be sending back more user friendly error strings from the
proc that are then passed back to the client?

What is a best practice that you would recommend?

Thanks,
Ron

[Peter Bradley]

Ysgrifennodd Ronald S. Cook:

How would you recommend passing errors back from your proc to your business
tier to the client (assuming you have full control of all layers)?

We use custom exceptions. That is, we throw an exception of an
appropriate type, with an appropriate message.

Our exceptions are defined in their own libraries, which we reference
wherever they are needed. Needless to say all our custom exceptions
share a namespace.
Peter