存储过程 API 的最佳实践? [英] Best practices for stored procedure API?

问题描述

我们正在向我们的产品添加一些可由 3rd 方客户端调用的存储过程.是否有参数验证、返回值、RAISERROR 等的最佳实践?

We are adding some stored procedures to our product that can be called by 3rd-party clients. Are there best practices for parameter validation, return values, RAISERROR, etc?

第 3 方客户端不能直接访问表，只能访问某些 sproc.sprocs 接触的表受到了很好的约束，但我们希望尽可能方便用户，当 sprocs 被错误调用时提供详细的错误信息.

3rd-party clients will not have direct table access, only to certain sprocs. The table touched by the sprocs is well-constrained but we want to be as user-friendly as possible as far as providing detailed error information when the sprocs are called incorrectly.

推荐答案

提供人类可以理解的信息性错误消息并不难.只是带有描述性文本的 RAISERROR.稍微困难一点的是提高本地化文本，这意味着正确使用 sp_addmessage 和家人.真正的难题是引发程序可以做出反应的错误.这意味着正确记录错误代码(和严重性和状态)，以及在 API 中使用它们的严格代码纪律.

Is not hard to provide informational error messages that a human can understand. Just RAISERROR with a descriptive text. slightly more difficult is to raise localized texts, which implies proper use of the sp_addmessage and family. The real hard problem is raising error to which a program can react. This means properly documented error codes (and severity and state), and severe code discipline in using them in your API.

并且不要忘记正确的事务嵌套.我的博客上有一个关于如何结合 T-SQL 异常正确处理事务的示例:异常处理和嵌套事务.

And don't forget proper transaction nesting. I have a sample on my blog on how to properly handle transactions in combination with T-SQL exceptions: Exception handling and nested transactions.

不幸的是，整个客户端/T-SQL 堆栈与异常相关的最新技术存在一些问题.最值得注意的是，如果您捕获 T-SQL 异常，则无法重新抛出它，因此您的客户端无法期待典型的系统错误号.请参阅 SQL Server:重新抛出异常与原始异常号码.这使您几乎没有办法传达正确的错误信息，除了在超过 50000 范围内使用您自己的错误编号之外，这非常麻烦，因为翻译"错误代码的数量增加了，并且使用错误消息字符串作为异常信息.

Unfortunately the state of the art on the whole client/T-SQL stack vis-a-vis exception has some problems. Most notable is that if you catch a T-SQL exception, you cannot rethrow it, so your client cannot expect the typical system error numbers. See SQL Server: Rethrow exception with the original exception number. This leaves you with little means to communicate proper error information, other than using your own error numbers on the over 50000 range, which is very cumbersome as the number of 'transalated' error codes increases, and using the error message string as the exception information.

这篇关于存储过程 API 的最佳实践?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！