This is obviously a rant, so buckle up.
I'm at the tail end of a little project adding some customizations to a legacy system. There isn't anything resembling a service (or business/domain) layer other than T-SQL SPROC's, and the vast majority of the business and validation logic is in the stored procedures. The clown that wrote a great deal of the original system (since departed) had a serious T-SQL fetish. The guy apparently truly believed that T-SQL was simpler and easier to maintain than C#. One of the results of this is that validation warnings like "Vendor does not exist" are created and generated by a stored procedure and passed back to the application by the message in the SqlException that's thrown by the command execution. Whatever is in the body of the exception, or any exception thrown for that matter, is automatically put into the audit tables where it is displayed to the end users on a web page. Mr. User, you ask why didn't your invoice go through? Because of "Object reference not set to an instance of an object." Duh. I don't know if there is any easy way to look at the exception to see if it is a validation error versus a system sql failure, but that's not the point, it's a stupid strategy.
A suggestion I'm making going forward is to handle system errors and validation errors separately. I'm not an expert on user interaction design, but I'm pretty sure you're not supposed to be sending raw exception messages to users. Plus it's just stupid to use exceptions for control logic. Not to mention it's incredibly moronic to create user messages in stored procedures. We know we'll need to provide some internationalization eventually. The idea that I'll have to scour stored procedures to localize the web displays pisses me off. Add in the fact that we need to support multiple database engines -- and I'm starting to get red-faced so I'll stop now.
Just a plea to my colleagues, let's please stop abusing stored procedures. Put business logic and UI code in predictable places and let's start thinking of better ways to do exception management. If you don't stop putting crap in sprocs, I'm gonna start getting angry and I'm bigger than you are.