In the execute statement, the optional clause with recompile comes after any parameters. It instructs Adaptive Server to compile a new plan, which is used for subsequent executions.
Use with recompile when you execute a procedure if your data has changed a great deal, or if the parameter you are supplying is atypical—that is, if you have reason to believe that the plan stored with the procedure might not be optimal for this execution of it.
Using execute procedure with recompile many times can adversely affect the procedure cache performance. Since a new plan is generated every time you use with recompile, a useful performance plan may age out of the cache if there is insufficient space for new plans.
If you use select * in your create procedure statement, the procedure, even if you use the with recompile option to execute, does not pick up any new columns added to the table. You must drop the procedure and re-create it.
