Cross Apply of UDF missing Results

Question

I am performing a SQL query similar to the following

SELECT fn.FullName, pn.LastName, pn.FirstName, pn.MI
FROM Source.dbo.tblPerson fn
    cross apply dbo.ParseFullName(fn.FullName) pn

the result looks fine for the first 85 rows the 86th+ always have NULL for columns derived from the UDF. If I add a where or order by clause to change the result set, it is always the first 85 rows that return the complete set of results. because the 85 number is so consistent, I am thinking that it is something that I am overlooking.

Any help that anyone can provide would be great

score 1 · Answer 1 · answered Oct 01 '12 at 21:47

1

Try replacing cross apply with outer apply. Make sure that your Sql won't bug out on the NULL.

SELECT fn.FullName, pn.LastName, pn.FirstName, pn.MI
FROM Source.dbo.tblPerson fn
OUTER APPLY dbo.ParseFullName(fn.FullName) pn

answered Oct 01 '12 at 21:47

jTC

1,340
9
17

Sounds like it might be an issue in the way that the UDF is parsing. Could you add that to your answer? How many rows are in your tblPerson, btw? – jTC Oct 02 '12 at 13:10
Thanks for the assistance. I figured out what my issue was. My answer is posted below. – TimN_FL Oct 02 '12 at 20:37

score 1 · Accepted Answer · answered Oct 02 '12 at 20:36

I figured out what the problem was. The UDF that was being called, as per the example, dbo.ParseFullName was call OLE Automation objects. Apparently, I was not properly destroying the objects via sp_OADestroy. I was destroying the initial object but not the all of the derived objects. Once I resolved that issue at the end of the UDF, I starting getting all of the results as expected. Thanks for all of the help.

Cross Apply of UDF missing Results

2 Answers2