Professional SQL Server™ 2005 Programming

11.5. Confirming Success or Failure with Return Values

Return values are used in a couple of different ways. The first is to actually return data, such as an identity value or the number of rows that the sproc affected. Consider this an evil practice from the dark ages. Instead, move on to the way that return values should be used and what they are really there for: determining the execution status of your sproc.

If it sounds like I have an opinion about how return values should be used, it's because I most definitely do. I was actually originally taught to use return values as a "trick" to get around having to use output parameters, in effect, as a shortcut. Happily, I overcame this training. The problem is that, like most shortcuts, you're cutting something out, and, in this case, what you're cutting out is rather important.

Using return values as a means of returning data to your calling routine clouds the meaning of the return code when you need to send back honest-to-goodness error codes. In short, don't go there!

Return values are all about indicating success or failure of the sproc and even the extent or nature of that success or failure. For the C programmers among you, this should be a fairly easy strategy to relate to. It's common practice to use a function's return value as a success code, with any non-zero value indicating some sort of problem. If you stick with the default return codes in SQL Server, you'll find that the same rules hold true.

11.5.1. How to Use RETURN ...