Actually this is kind of same issue that I blogged about way back in last year.  At that time it was all about view, but I didn’t knew that same limitation exists for functions (aka UDF) as well. Exactly like view, in function also when one function (child function) is calling another function (parent function) and if that underlying function gets changed it is “generally” required to re-compile other function as well. (generally because, if you are not changing structure of data returned by underlying table then you are not required to re-compile child function).

Today in evening, I was tasked with making some changes in a function that was used to generate reports for our client. This function was actually combo of multiple functions that were doing different tasks. Now, I changed some logic of that parent functions as when I checked my new report, I was surprised to see that results were a bit of “off”. Though they were not completely wrong some data was not making any sense at all. I thought may be I screwed up something in my code so I check whole report again, I checked all functions to be sure that they are returning correct result and I found nothing. Weird thing was that report was showing OK data in staging but was not showing properly in production,even though both are ideally same.

But after wasting 2 hours, I finally asked about this issue to my mentor. He too got confused with this issue then he remembered similar incident when he was having same trouble of getting proper data and for try we recompiled the child function which was using those parent functions. And with surprise, when we ran the report again it was OK !!!.

More >