Is the Execution Path for a Function invoked inside a Stored Procedure cached? According to some of my research a benefit of using Stored Procedures over Functions is that they cache an execution path which provides performance benefit due to not having to recalculate it.

So I was wondering if I invoke a Function from within a Stored Procedure is the entire Stored Procedure still cached?

Here's a mocked up example function - it would be used to add further details onto a table selected in the Stored Procedure.

CREATE FUNCTION [dbo].[fnExampleFunction]
(@EmployeeDetails   EMPLOYEEDETAILS READONLY
)
RETURNS @retEmployees TABLE
(AppraisalLevelId INT,
 JobTitleId  INT,
 LocationId INT,
 Name1 nvarchar(200),
 Name2 nvarchar(200),
 Name3 nvarchar(200)
)
AS
     BEGIN
         INSERT INTO @retEmployees
                SELECT 
             ed.*,
              jt.JobTitleName,
              l.LocationName,
              al.AppraisalLevelName
                FROM @EmployeeDetails ed
                     LEFT OUTER JOIN AppraisalLevels al ON al.AppraisalLevelId = ed.AppraisalLevelId
                     LEFT OUTER JOIN EmployeeDetails m ON m.EmployeeId = ed.ManagerId
                     LEFT OUTER JOIN JobTitles jt ON jt.JobTitleId = ed.JobTitleId
                     LEFT OUTER JOIN Locations l ON l.LocationId = ed.LocationId;
         RETURN;
     END;
GO


So the Stored Procedure in psuedo would be used IF @Condition Return SelectedTable ELSE Return fnExampleFunction(SelectedTable)
First of all, why do you use multi-statement table-valued functions instead of inline function?  Multi-statement function calls are expensive and introduce significant CPU overhead. 

Second, the way you want to use it is very strange:


  The Stored Procedure in psuedo would be used IF @Condition Return
  SelectedTable ELSE Return fnExampleFunction(SelectedTable)


You just cannot return tables from sp using Return.

And now to your main question:


  So I was wondering if I invoke a Function from within a Stored
  Procedure is the entire Stored Procedure still cached?


Of course stored procedure's plan will be cached.

There is a difference of what is cached when you use multi-statement function  (which starts with a  BEGIN  and ends with an  END  keyword) or inline function:

Multi-statement functions (table-valued or scalar) have query plans on their own. Inline functions don't. SQL Server expands and embeds inline table-valued functions into the actual queries, similar to regular views, and it optimizes their statements as part of the queries. As a result, there are no separate calls of the function and you don’t have to deal with its associated overhead. 

Here is how your multi-statement function can be re-written as inline function:

CREATE FUNCTION [dbo].[fnExampleFunction]
(
    @EmployeeDetails   EMPLOYEEDETAILS READONLY
)
RETURNS TABLE AS RETURN
(
    SELECT 
    ed.*,
    jt.JobTitleName,
    l.LocationName,
    al.AppraisalLevelName
    FROM @EmployeeDetails ed
            LEFT OUTER JOIN AppraisalLevels al ON al.AppraisalLevelId = ed.AppraisalLevelId
            LEFT OUTER JOIN EmployeeDetails m ON m.EmployeeId = ed.ManagerId
            LEFT OUTER JOIN JobTitles jt ON jt.JobTitleId = ed.JobTitleId
            LEFT OUTER JOIN Locations l ON l.LocationId = ed.LocationId
)