問題描述
我們正在向我們的產品添加一些可由 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 的最佳實踐?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！