T-sql – Is the Execution Path for a Function invoked inside a Stored Procedure cached

t-sql

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)

Best Answer

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
)