Executing a stored procedure saves abstract plans for each statement that can be optimized, even if the stored procedure contains control-of-flow statements that can cause different statements to be run, depending on parameters to the procedure or other conditions.
Adaptive Server loads and saves the abstract plans when the stored procedures are compiled, not when they are executed.
When Adaptive Server compiles a stored procedure (usually when it is first run), it saves an abstract plan for each optimized statement. Adaptive Server does not influence the abstract plan capture, or whether the stored procedure contains control-of-flow statements that cause different statements to be executed, depending on the procedure’s parameters.
If you run the query a second time (without recompilation) with different parameters that use a different code path, because Adaptive Server already optimized and saved the plans for all statements from the earlier compilation, both the plans and the abstract plans for the statements in this different code path are available, and are based on the prior stored procedure’s run parameter values, whether or not these statement were executed.
However, abstract plans for procedures do not solve the problem caused by procedures with statements that are optimized differently depending on conditions or parameters. For example is a procedure where users provide the low and high values for a between clause, with a query such as:
select title_id from titles where price between @lo and @hi
Depending on the parameters, the best plan could either be an index access or a table scan. The abstract plan may specify either access method, depending on the parameters used for the initial execution of the procedure. Abstract plans that are saved while executing queries or stored procedures in tempdb are lost if the server is restarted.
