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)