BEST approach to share messages between data tier and application tier

Question

Hi there

I was looking for any suggestion or a good approach to handle messages between the data & application tier, what i mean with this is when using store procedures or even using direct SQL statements in the application, there should be a way the data tier notifies upper layers about statement/operation results in at least an organized way.

What i commonly use is two variables in every store procedure:

@code INT,
@message VARCHAR(1024)

I put the DML statements inside a TRY CATCH block and at the end of the try block, set both variables to certain status meaning everything went ok, as you might be thinking i do the opposite on the catch block, set both variables to some failure code if needed perform some error handling.

After the TRY CATCH block i return the result using a record:

SELECT @code AS code, @message AS message

These two variables are used in upper tiers for validation purposes as sending messages to the user, also for committing o rolling back transactions.

Perhaps i'm missing important features like RAISERROR or not cosidering better and more optimal and secure approaches.

I would appreciate your advices and good practices, not asking for cooks recipes there's no need, just the idea but if you decide to include examples they'd be more than welcome.

Thanks

Answer 1

My experience has been to rely on the inherent workings of the procedure mechanisms. What I mean by this is that if a procedure is to fail, either the implicit raised error (if not using try/catch . . . simply CRUD stuff) or using the explicit RAISERROR. This means that if the code falls into the catch block and I want to notify the application of the error, I would re-raise the error with RAISERROR. In the past, I've have created a custom rethrow procedure so I can trap and log the error information. One could use the rethrow as an opportunity to throw a custom message/code that could be used to guide the application down a different decision path. If the calling application doesn't receive an error on execution, it's safe to assume that it succeeded - so explicit transaction committing can commence.

I've never attempted to write in my own communication mechanism for interaction between the app and the stored proc - the objects in the languages I interact with have events or mechanisms to detect the raised error from the procedure. It sounds to me like your approach doesn't necessarily require too much more code than standard error handling, but I do find it confusing as it seems to be a reinventing the wheel type of scenario.

I hope I've not misunderstood what you are doing, nor do I want to come off as critical; I do believe, however, you have this feedback mechanism available with the standard API and are probably making this too complicated. I would recommend reading up on RAISERROR and see if it's going to meet your needs (see the Using RAISERROR link for this at the bottom of the page as well).

Answer 2

The generally accepted approach for dealing with this is to have your sproc set a return values and use raise error as has been previously suggested.

The key thing that I suspect you are missing is how to get this data back on the client side.

If you are in a .net environment, you can check for the return value as part of the ado.net code. The raise error messages can be captured by catching the SqlException object and iterating through the errors collection.

Alternatively, you could create and event handler and attach to the connection InfoMessage event. This would allow you to get the messages as they are generated from sql server instead of at the completion of the batch or stored procedure.

another option which I wouldn't recommend, would be to track all of the things you care about into a XML message and return that from the stored proc this gives you a little more structure then your free text field approach.