验证存储过程参数以强制引用完整性约束 [英] Validating stored procedure arguments to enforce referential integrity constraints

问题描述

最近，我倾向于在实际执行查询之前先验证存储过程操作查询参数。一个示例是检查在更新具有列 C的唯一索引的表 T时，更新不会失败...通过首先执行在唯一索引上存在选择类型在实际更新之前进行查询。如果会违反唯一索引，那么我将尽早捕获错误并返回错误。

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.

问题是，我才刚刚开始这样做，我无法确定这是正确的选择，还是应该让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.

推荐答案

对我来说，这似乎是很多不必要的工作。

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.

已添加

我对此的另一种想法是，您可能正在数据库级别上捕获到应该在业务逻辑或UI级别上捕获（或者至少可以更优雅地捕获）的错误。

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.

这篇关于验证存储过程参数以强制引用完整性约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！