index seek while calling the UDF itself, index scan while wrapping the UDF in sp

Question

Hi,

I have a UDF that does most of the joins and when I run it, it read about 100-150 pages (from sql profiler) but if I wrap this UDF in sp then the sp will do the read about 243287 pages.

Furthermore, the UDF itself performs index seek but the sp performs index scan on one of the searched columns.

Can anyone please advise?

Answer 1

Based on your comments about (custid = @param1 OR @param1 is null) vs. (custid = @param1), I think Gail Shaw's blog posting on Catch-all queries will provide some insight into what you're seeing here. Basically, I think you're getting an execution plan cached for the @param1 is null case that then performs poorly when you pass in a real @param1.