In the create procedure statement, the optional clause with recompile comes immediately before the SQL statements. It instructs Adaptive Server not to save a plan for this procedure. A new plan is created each time the procedure is executed.
In the absence of with recompile, Adaptive Server stores the execution plan that it created. Usually, this execution plan is satisfactory.
However, a change in the data or parameter values supplied for subsequent executions may cause Adaptive Server to create an execution plan that is different from the one it created when the procedure was first executed. In such situations, Adaptive Server needs a new execution plan.
Use with recompile in a create procedure statement when you think you need a new plan. See the Reference Manual: Commands.
