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

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 p开发者_C百科ages.

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

Can anyone please advise?

---

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.