Best practices for stored procedure API?

Question

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.

Answer 1

• Use TRY/CATCH blocks
• Throw meaningful messages (I use a prefix such as "INFO:" to distinguish my errors from database errors)

Example:

SET NOCOUNT, XACT_ABORT ON
...
BEGIN TRY
    IF @parameter1 IS NULL
        RAISERROR ('INFO: "parameter1" should not be blank', 16, 1)

    IF @parameter2 < 0
        RAISERROR ('INFO: "parameter2" must be greate then zero', 16, 1)

    ...

END TRY
BEGIN CATCH
    DECLARE @MyError nvarchar(2048)
    SELECT @MyError = ERROR_MESSAGE() -- + other stuff, like stored proc name perhaps
    RAISERROR (@MyError, 16, 1)
    ...
END CATCH

Answer 2

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.

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.

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.