I'd like to confirm that using the return value to return error messages is an excellent use of the return value. It makes stored procedures much like functions. In our architecture, we keep a table of business-specific errors. Consumers of our stored procedures can query the buiness error table to get a user-friendly description of the return code from the stored procedure.

Return codes can also be useful in communicating the number of rows that were affected by a complex procedure. For example, a procedure that is changing a flag based on a complex set of criteria can return how many records were changed. We use the return code both ways. Errors are always negative numbers and "affected records" is always a positive number.