What is the best way to return an error from a TSQL Proc?

Question

Here’s the scenario:

1. You load a page, which renders based on the data in the database (call these the “assumptions”)
2. Someone changes the assumptions
3. You submit your page
4. ERROR!

The general pattern to solve this problem is this (right?):

In your save proc, inside a begin and commit transaction, you validate your assumptions first. If any of them changed, you should return a graceful error message, something like an XML list of the ID’s you had problems with, that you handle in the page rather than let it be handled by the default error handling infrastructure.

So my question is what is the best way to do that?

• Return the xml list and an error flag in out parameters that are unset and 0 if it completes correctly?
• Use an out parameter to return an error status, and the result of the proc be either the error list or the valid results of the proc?
• Something else? (I should note that raiseerror would cause the proc to be in error, and get picked up by the default error handling code)

Update: I would like to return a maniplatable list of IDs that failed (I plan to highlight those cells in the application). I could return them in CSV format in RAISEERROR, but that seems dirty.

Answer 1

Use the RAISERROR function with the appropriate severity and/or wait level. If you use a low severity this does not necessarily cause an exception, as you contend, and with .Net at least its pretty simple to retrieve these messages. The only downside is that with the StoredProcedure command type in .Net messages are only pumped in groups of 50.

Stored procedures can return multiple result sets. Based on your update, you could also insert errored ids into a temporary table, and then at the end of your procedure select the records from that table as an additional result set you can look at.

Answer 2

I would do an output parameter with a message, the return will already have something which is not 0 if there is an error

also be careful with doomed transaction and check with xact_error, see Use XACT_ABORT to roll back non trapable error transactions

Answer 3

I agree - I like RAISEERROR:

--  Validate @whatever 
IF @whatever >= '5'
BEGIN
    RAISERROR ('Invalid value for @whatever - expected a value less than 5, but received %s.', 10, 1, @whatever)
    RETURN 50000
END;