Stored Procs - Best way to pass messages back to user application

Question

I'd like know what people think about using RAISERROR in stored procedures to pass back user messages (i.e. business related messages, not error messages) to the application.

Some of the senior developers in my firm have been using this method and catching the SqlException in our C# code to pick up the messages and display them to the user. I am not happy with this method and would like to know how other people deal with these types of user messages from stored procs.

Answer 1

I've done this, but it was usually to pass along business "error" messages, essentially a data configuration had to be in place that couldn't be enforced with standard FK constraints for whatever reason.

If they are actually "errors", I don't have much of a problem with it. If it's inserting a record and using RAISERROR to throw ("You have successfully registered for XYZ!"), then you've got a problem. If that was the case, I'd probably come up with a team/department/company development standard for using out parameters.

Answer 2

Using RAISERROR like this is really not a good idea. It's just like using Exceptions as flow control logic, which is generally frowned upon.

Why not use an OUT parameter instead? That's exactly what they are for. I can't think of a database or a client API that doesn't support OUT parameters.

Answer 3

Make your stored procedure return 2 sets of data. The first can contain the actual returned data, then the second can return a text message. Your app code can then use the data where it needs to, then display whatever message comes back.