I've run into this issue. It basically comes down to the plan that is being used inside the function is not the same as the plan used when you issue the query manually outside of the function. Although I'm no expert on when plans are prepared and re-evaluated for functions, I know that they are not re-evaluated each time to execute the function.
So, what I did in such cases was to build up the sql query in a text variable inside my function, and then use the EXECUTE command inside the function. When you use the EXECUTE command, the plan is prepared each time. I know there is some minimal overhead of preparing the plan each time, but it seems like it's minor compared to the saving's you'll get.
- Mark
