sjimmo (5/12/2010)
It slips me right this moment which hint it is, but for the scenario you describe, there is a hint that can be used in order to have the procedure use the queryplan for the longest running parameter.
I think you are referring to the OPTIMIZE FOR option, right?
You can use this to force optimization for the "most often" used vlaue (getting the best performance in most cases, but worse -potentially much worse- in rare cases).
Or you can use it to force optimization for a value that will result in a plan that might not be optimal for all values, but will never hit extreme execution times.
[funny fact - I am typing this right after identifying a query ona customer site where the optimizer decided on a nested loops join and a table scan, based on an estimate of 1 input row; the actual row count turned out to be over 20,000 so a 25,000+-row table got scanned over 20,000 times... - that would be the extreme execution time]