最佳做法存储过程API？ [英] Best practices for stored procedure API?

问题描述

我们正在添加一些存储过程，以我们的产品，可以通过第三方客户端调用。是否有参数验证，返回值，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?

第三方客户端将不能直接访问表，只有特定的存储过程。在存储过程的感动表是良好约束，但我们希望能为用户友好尽可能远的提供，当存储过程被称为不正确详细的错误信息。

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 和家人。真正的难题是提高错误一个程序可以反应。这意味着妥善记录错误codeS（和的严重性的和的状态），以及严重code学科在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：<一href=\"http://stackoverflow.com/questions/1882788/sql-server-rethrow-exception-with-the-original-exception-number/1883148#1883148\">Rethrow除了与原来的异常编号。这留给你小装置比使用在50000范围内，这是由于transalated'错误codeS的数量增加，并使用错误消息字符串作为非常麻烦自己的错误编号，以传达适当的错误信息，其他异常信息。

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屋！