Validating stored procedure arguments to enforce referential integrity constraints

Question

Hi all,

Of late, I have a tendency to validate my stored procedure action query arguments before actually performing the query. An example would be to check that, on update to table 'T' that has a column 'C' which has a unique index, that the Update would not fail... by first performing a "Select Exists on the unique index" type of query before the actual Update. If the unique index would be violated I am therefore trapping the error early and returning.

I have basically built on my usual optimistic locking strategy of examining that the record I am updating is still eligible for update i.e. hasn't changed. I have taken it a step further by testing that the unique index would not be violated if I were to perform the Update.

The thing is, I've only recently started doing this and I can't decide if it's the right thing to do or I should just let the Update handle the unique index failure.

Does anyone have any sound advice in this area?

I'm also interested in other people's approaches to parameter validation in general.

Answer 1

It seems like a lot of unnecessary work for me.

Also, assuming you're just changing the return value, you're taking what would normally be an exception and turning it into a stored procedure that returns a different value. This could lead to logic errors, which are harder to track.

Added

The other thought I had on this is that you may be catching errors at the DB level that should be caught (or at least could be caught more gracefully) at the Business Logic or UI level.