Reasons for Using Both RETURN() and RAISERROR()
This chapter is from the book
This chapter is from the book
This chapter is from the book
You might think that I'm using both a safety net and a wire because I use both RAISERROR() and RETURN() of the error code when an error is encountered. I used to think so too, but this is still the best solution I'm aware of. First of all, the caller must be aware of an error so that it can take the correct action. For example, the caller probably shouldn't make another stored procedure call after an error, but should just go to the ExitHandler instead and end the stored procedure. Second, I want to have as much contextual information as possible for the logging solution.
Using RETURN() for communicating the result of a stored procedure is considered a de facto standard among T-SQL programmers. The RETURN() value can also be used from the .NET component, which would then know what went wrong, although the information will often be vague. For example, the following questions can't be answered:
Where in the call stack of the stored procedures did the error occur?
Which errors did the involved stored procedures raise from the error and "up" (that is, the error stack)?
Where in the stored procedure did the error occur?
This last question is an interesting one if the same error could occur in several places in one stored procedure, which is a common situation.
The other approach is to use RAISERROR() instead of RETURN(). This solves the problems concerning lack of context information. However, RAISERROR() introduces new problems in the context of stored procedures. There are two ways to use RAISERROR(). You can either use RAISERROR() with custom error codes (which means 50000 or higher, and the code must have been added in the sysmessages table), or you can use RAISERROR() with a string.
For example, assume that you catch the @@ERROR for a duplicate key (the duplicate key error code is 2627) and want to tell the calling stored procedure about the error. The problems you will encounter are
You can't raise that error to the caller because RAISERROR() may not use error codes of less than 50000.
If you put the error 2627 together with a description of where the problem is in a string and use that string with RAISERROR(), the caller stored procedure can't get to the string. Typically, the caller won't catch an error at all, but you can at least have that effect by using WITH SETERROR with RAISERROR() to give @@ERROR a value.
I could create new error codes by just adding 50000 to each one of those that already exists in sysmessages. I could then catch reserved errors, raise them again by taking their old value and adding 50000, and then use WITH SETERROR. (In this case, I would also have to add my custom error codes to sysmessages.) The advantage would be that I never have to check the return value of a stored procedure but only @@ERROR, but I find this technique to be clumsy and a bit of a hack. Thus, my conclusion remains to use both RETURN() and RAISERROR() and let them solve different problems, so to speak. This solution works very well, although I do have to check for both return values and @@ERROR after a stored procedure call.
Another reason for using RETURN() to communicate problems back to the components is that when SQL Server raises an error (such as 2627 because of duplicate keys, for example), it will be visible to the components as an exception, even if the stored procedure is prepared for the problem and handles it gracefully. In this case, the RETURN() will send 0 and the component knows that the exception can be skipped.
NOTE
You can decrease the number of situations in which SQL Server raises errors that the component shouldn't see by taking proactive actions. Before you do an INSERT that you know might give a duplicate key error, you can check the situation with a SELECT first.