Should stored procedures check for potential issues when executing?

Question

Let me explain this question a bit :)

I'm writing a bunch of stored procedures for a new product. They will only ever be called by the c# application, written by the developers who are following the same tech spec I've been given. I cant go into the real tech spec, so I'll give an close enough example:

In the tech spec, we're having to store file data in a couple of proprietary zip files, with a database storing the names and locations of each file within a zip (eg, one database for each zip file)

Now, lets say that this tech spec states that, to perform "Operation A", the following steps must be done:

1: Calculate the space requirements of the file to be added

2: Get a list of zip files and their database connection strings (call stored proc "GetZips")

2: Find a suitable location within the zip file to store the file (call stored proc "GetSuitableFileLocation" against each database connection, until a suitable one is found)

3: In step 2, you will be provided with a start/end point within the zip to add your file. Call the "AllocateLocationToFile" stored proc, passing in these values, then add your file to the zip.

OK - so the question is, should "AllocateLocationToFile" re-check the specified start/end points are still "free", and if not, raise an exception?

There was a bit of a discussion about this in the office, and whilst I believe it should check and raise, others believe that it should not, as there is no need due to the developer calling "GetSuitableFileLocation" immediately beforehand.

Can I ask for some valued oppinions?

Answer 1

Generally, it is better to be as safe as possible. A calling code should never rely on an external code (the sps are kind of external). The idea is that you can not predict what would happen in the future. New guys come to the company... the sps are given to another team and so on...

Personally, the fact that B() is right after A() doesn't guarantee anything. To change this for whatever reason is not something to be considered impossible.

A team should never take decisions based on "we are going to maintain this, no problem at all" because they might get fired, the company may sell the product and so on..

My suggestion is to do the checking, profile the code and if it is really a bottleneck to remove it, but write somewhere that THIS CAN BREAK!.

Answer 2

Given that you're manipulating files, with all the potential havoc this can create, I'd say in this scenario the risk (damage component) is high enough to be cautious.

And Svetlozar's right: what if great success does cause re-use, or other added-on applications? Not everyone may be as well-behaved as your team is right now.

Answer 3

One reason why it might be a good idea would involve race conditions. Is it possible two users could call the process at the same time and get the same values? Please at least test this scenario with the currently designed process.