In my opinion, I always suggest to use return values as a Success/Failure indication. In the project I am working on, I have some nested stored procedures. spX calls spY and spZ within a transaction.

CREATE PROCEDURE spX

AS

    BEGIN TRAN

         EXEC spY

         EXEC spZ

    END TRAN

GO

In this case, if an error occured in spY, you can't tell about it, and the execution will continue to call spZ without rolling back the transaction, which is bad logic!

So I guess the best way to make use of return values is as follows:

CREATE PROCEDURE spX

AS

    BEGIN TRAN

         Declare @retval INT

         EXEC @retval = spY

         IF @retval IS NOT 0

             ROLLBACK TRAN

         EXEC @retval = spZ

         IF @retval IS NOT 0

             ROLLBACK TRAN

    END TRAN

GO

CREATE PROCEDURE spY

AS

    -- do whatever SQL statements you want here...

    RETURN @@ERROR

GO

CREATE PROCEDURE spZ

AS

    -- do whatever SQL statements you want here...

    RETURN @@ERROR

GO