Geek Answers Handbook

Best practices for stored procedure APIs?

We add some stored procedures to our product that may be called by third-party customers. Are there better methods for checking parameters, return values, RAISERROR, etc.?

Third-party clients will not have direct access to the table, only for certain sprocs. The table affected by sprocs is well limited, but we want to be as user-friendly as possible because we provide detailed information about errors when sprocs are called incorrectly.

+5
source share
2 answers

It is easy to provide informational error messages that a person can understand. Just RAISERROR with descriptive text. it’s a little harder to pick up localized texts, which implies the proper use of sp_addmessage and family. The real difficult problem is that there is an error with which the program may react. This means correctly documented error codes (both severity and condition), as well as tight code discipline when using them in your API.

And do not forget about the correct nesting of transactions. I have a sample of my blog on how to properly handle transactions in conjunction with T-SQL exceptions : Exception handling and nested transactions .

, / T-SQL . , T-SQL, , . . SQL Server: . , 50000, , "" .

+2
  • TRY/CATCH
  • ( , "INFO:", )

:

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
+3

More articles:


All Articles