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

If it can't be checked/caught earlier it might be difficult to do anything else.

I've had to write a raiseerror in a procedure I wrote and used as a constraint on inserting/updating a table because it was the 'last stop' for the data and I had to check it there.

I think in general if you are getting errors back from the DB.. its a pain in the butt and harder to give 'nice' feedback to the user without a lot of effort, but sometimes you just don't know until you insert/update :P

Answer 2

I would try to avoid getting my stored procs from returning Business Related messages because by definition these kind of messages probably ought to be handled/generated in a Business Logic tier.

Execeptions should be exceptional (infrequent). I would use RAISEERROR only for errors (like hey I tried to import all this data and one of the rows had goofy data so I rolled back the transaction). You also need to be very careful with the severity of the error raised this can have a huge affect on how the error propogates and what happens to your connection.

Try using a return value or an output variable if this isn't enough.

Answer 3

My blasphemous 2 cents:

Text-based messages work really well on the web, like, HTTP for example. It's easy to create, send, debug systems where the messaging is done in human-readable text.

So, maybe the same thing with the messaging between your SQL Server layer and the layer above it. Using text as part of the messaging might be making your development more agile and your debugging easier. Maybe the senior developers are being pragmatic and you should maybe be open to setting aside pre-conceived notions of correctness.

Debate the design choice on its own merits, not based on notions of correctness. (There is fashion in software development too)

Answer 4

I guess if you don't mind messing with checking columns and such you could return something different based on what happened.

If everything is fine, return the data as normal. If something isn't fine, return a result with a column named Error that describes what was bad. Check the column names for this column before you process data and act accordingly.

Off the top of my head if you really object to RAISERROR.

Answer 5

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 6

I've used raiseerror to return from the depths of multiple nested stored procedures, but the final layer of stored procedure always catches the exception prior to being raised to the calling language (in our case Java via JDBC). The stored procedure catch in the outer most layer is transformed into an XML message to be transported to the JDBC call and the root element, by our convention, must contain a feedback attribute. The value of the feedback attribute always has a decorator of either ok, alert, or error. Ok means go on, nothing to see here. Alert means go on, but show the rest of the feedback to the user. Error means punt, call the help desk.

Answer 7

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 8

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.

Answer 9

I'd use the RETURN value from the stored procedure, like this:

CREATE PROCEDURE checkReturnValue
AS
BEGIN
    DECLARE @err AS INT
    SET @err = 0

    IF (rand() < 0.5)
    BEGIN
     SET @err = 1
    END

    SELECT * FROM table

    PRINT @err

    RETURN @err
END

Check the RETURN value in your application calling the stored procedure.

Answer 10

Only if the "business" error messages are database error messages in the sense that the database constraints which have been put in place to satisfy basic low-level business requirements at the database level are being violated.

It should not be used for high-level business logic, which should be avoided in the database layer. The database layer is always the slowest to change, so only very slowly changing and unchanging business logic should be there.

So maybe yes for message about order for an inactive/disabled customer, but not for an order for a customer who has a balance in 90 days. The first rule may be permanent, the second is likely to be configurable, subject to whims of the business on a monthly basis.

Answer 11

We raise errors when errors occur, and we return status information in output variables or return values.

Answer 12

You should be using SQL stored procedure output parameters.

From http://msdn.microsoft.com/en-us/library/ms378108.aspx:

CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @msg varchar(50) OUTPUT
AS
BEGIN
   SELECT ManagerID 
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID

   SELECT @msg = 'here is my message'
END

Answer 13

Is it bad form to answer a question this old? Anywho...

For your everyday status messages, this would be a Bad Thing and I agree with pretty much every answer above. I have however seen this used quite effectively for showing progress during long batches. See Getting feedback / progress from batches and stored procedures by Jens K for an example. You've got to have a pretty hardcore reason for doing it, but when you need it, you need it and it is awesome.

Answer 14

Exceptions should be thrown only when the situation is exceptional and you do not have a handling logic. Also raising and catching exception is expensive.

Avoid using exceptions unless

1. The situation is exceptional
2. You do not have handling logic for the exceptional situation

Use output parameters or return multiple resultsets to pass info from the stored procedures.