Error Handling: @rtnVal <> 0

After calling a stored procedure from within a stored procedure, check the return value of the called procedure.  If the return value is non-zero, the stored procedure returned an error (assuming the procedure follows that standard).

If procedure A calls procedure B and procedure B has an error, procedure B should call RAISERROR and return a non-zero value.  Procedure A can call RAISERROR and then return a non-zero value or it can just return the non-zero value without calling RAISERROR.

Exceptions:

  1. For stored procedures that return a non-zero return value but do not call RAISERROR, use method 1.
  2. For stored procedures that have proper error checking and reporting return a non-zero value on error, use method 1 or method 2.
  3. For stored procedures that do not have proper error checking at all, it is best to add the proper error checking and reporting and then use either method 1 or 2 in the calling procedure.

Method 1:

CREATE PROCEDURE dbo.RGExampleExecCall (
...
    EXEC @rtnVal = dbo.RGUpdateUserLastName
...
    IF @rtnVal <> 0 BEGIN
        EXEC dbo.TagValueList @list OUTPUT, 'UserID', @UserID, 'LastName', @LastName
        RAISERROR(990008, 16, 1, @rtnVal, @spName, 'RGUpdateUserLastName', @list)
        RETURN(@rtnVal)
    END
...
END

Method 2:

CREATE PROCEDURE dbo.RGExampleExecCall (
...
    EXEC @rtnVal = dbo.RGUpdateUserLastName
...
    IF @rtnVal <> 0 BEGIN
        RETURN(@rtnVal)
    END
...
END