Example Stored Procedure: EXEC another Stored Procedure

/***********************************************************************************************/
PRINT 'RGExampleExecCall';
/***********************************************************************************************/
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'RGExampleExecCall' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
    DROP PROCEDURE dbo.RGExampleExecCall
GO
CREATE PROCEDURE dbo.RGExampleExecCall (
    @UserID   int,
    @LastName varchar(32)
) AS
/*
    An example stored procedure
*/
BEGIN
    SET NOCOUNT ON

    DECLARE @error    int
    DECLARE @list     varchar(512)
    DECLARE @rowcount int
    DECLARE @rtnVal   int
    DECLARE @spName   sysname

    SELECT @rtnVal = NULL
    EXEC @rtnVal = dbo.RGUpdateUserLastName
        @UserID   = @UserID,
        @LastName = @LastName

    IF @rtnVal IS NULL OR @rtnVal <> 0 BEGIN
        EXEC dbo.TagValueList @list OUTPUT, N'UserID', @UserID, N'LastName', @LastName

        IF @rtnVal IS NULL BEGIN
            RAISERROR(990014, 16, 1, @spName, 'RGUpdateUserLastName', @list) WITH SETERROR
            RETURN(@@error)
        END
        ELSE IF @rtnVal <> 0 BEGIN
            [Begin optional section]
            RAISERROR(990008, 16, 1, @rtnVal, @spName, 'RGUpdateUserLastName', @list) WITH SETERROR
            [End optional section]
            RETURN(@rtnVal)
        END
    END

    RETURN(0)
END
GO