SQL Server Function … and two hours of my life

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 !!!.

So ideally when you execute child function whose parent function is changed, you will get exception but for some reasons our code was not generating exception instead it was just showing wrong result !! I don’t think I will be able to reproduce that issue again since it was way too much complex function but for try I played with some simple queries to show that ideally we should get exception.

--Parent  Function
create function Foo(@country varchar(20))
returns table
as
return
(select FirstName,LastName,AddressLine1,City,[State/Province] from EmpAddress where Country=@country);
go

-- Child Function
create function bar(@city varchar(20))
returns table
as
return
( select FirstName,LastName,AddressLine1,City,[State/Province] from Foo('United States') where City=@city)
go


select * from Foo('United States')
select * from bar('Renton')
go

alter function Foo(@country varchar(20))
returns table
as
return
(select FirstName,LastName,AddressLine1,City,Country from EmpAddress where Country=@country);
go

-- This statement should generate error
select * from bar('Renton')

So, in the end even though I felt like 2 hours of my life were wasted but I learned something very important and very hard way that if you have nesting functions its always better to recompile child function as well Open-mouthed smile

That’s it for now …

It’s Just a thought … Fingers crossed

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *