I've just finished a(nother) SQL course and this exact senario was discussed with both of these solutions proposed. i.e. using WITH RECOMPILE or using an itermediate SP to decide which final SP to call, based on the data range. We have this problem with one of our apps at the moment.

Has anyone used the DBCC FREEPROCCACHE call? Say using it on a nightly or weekly basis from a job? This would force a recompile of all procs

Leo