存储过程中调用的函数执行路径是否缓存？含函数的存储过程仍会缓存吗？

Great question—this is a super common point of confusion when mixing stored procedures and functions, so let’s break it down clearly.

核心结论 #

Yes, your stored procedure’s execution plan will still be cached even if it calls functions. The fact that you’re invoking a function inside the procedure doesn’t negate the storage engine’s ability to cache the procedure’s overall execution path (including your IF @Condition branching logic).

关键细节：函数类型会影响整体性能（但不影响存储过程缓存） #

The catch here is that the type of function you’re calling will impact how efficiently the combined logic runs, even though the procedure itself stays cached:

• Scalar functions (your example sounds like this, since it adds detail to query results):
  These run on a row-by-row basis (often called RBAR—Row-By-Agonizing-Row) by default. While the stored procedure’s plan is cached, the scalar function itself may not have its execution plan tightly integrated with the procedure’s. If it’s a deterministic scalar function (same input always returns same output), SQL Server might cache its results for repeated calls with the same parameters, but non-deterministic functions (like those using GETDATE()) will recalculate every time. Either way, the procedure’s own execution path remains cached.

• Inline Table-Valued Functions (ITVFs):
  These are treated like parameterized views—SQL Server "expands" their logic directly into the stored procedure’s execution plan. That means the entire combined query (procedure + inline function) gets cached as a single plan, leading to performance similar to writing the function’s logic directly in the procedure. This is the most efficient scenario.

• Multi-Statement Table-Valued Functions (MSTVFs):
  These act like mini-stored procedures with their own separate execution plans. The parent stored procedure’s plan will still be cached, but it will include a call to the MSTVF’s plan (which may also be cached separately). MSTVFs tend to be less efficient than ITVFs because they often involve intermediate temp tables and don’t integrate as cleanly with the parent query’s plan.

一个额外注意点 #

If you modify the definition of the function being called (e.g., change its logic), SQL Server will mark the dependent stored procedure’s cached plan as invalid. The next time you run the procedure, it will recompile and generate a new cached plan that accounts for the updated function.

内容的提问来源于stack exchange，提问作者Amicable