October 10, 2006 at 4:59 am
Hi,
Can anyone explain why this doesn't work:
select
*,
(select sum(time_normal_hrs) from dbo.fn_GetJobs(e.[id],9,2006)) 'hours'
from tbl_employee e
---
Where fn_GetJobs is a UDF that returns a table variable. It works if I just call it with a bunch of explicit parameters, but not nested as above. I was hoping to cut down on code in a big, unreadable query by using UDFs...
Cheers,
Chris
October 10, 2006 at 5:22 am
Hi Chris,
I think this is the same thing that was discussed here several times - you could find some references to it using the search tool - but in short, problem is that your function returns table. In your query, it would return one table for each set of parameters (each row). How would you handle 10000 tables returned by the function? As far as I know, this is simply impossible and not allowed. You can only nest the function into a query if it returns 1 value.
I can see that you sum the result of function afterwards. If you create a function that will include this aggregation and will return one value - sum of normal hours - you can use it directly in a query like yours. However, be warned that usage of functions in places like this may make the code easier to read, but also can (and probably will) adversely influence the performance on results with several thousand rows. UDF means that the query will be processed row-by-row instead of standard set-based approach.
October 10, 2006 at 5:31 am
Hi Vladan,
Thanks for replying.
I was aware of the performance implications and willing to suffer them - it's a reporting system and wouldn't have too many rows.
I know I could change the function to return a single value, but I had a WHERE clause too (I stripped down the version I pasted above for simplicity). I guess I could put the clause within the UDF and pass another parameter...
I'm surprised that it doesn't execute at all, though. Oh well, you live and learn.
Thanks,
Chris
October 10, 2006 at 5:32 am
Ah yes, almost forgot that I also meant to say...
In terms of how SQL Server executes my statement, how is that different to using a subquery?
Chris
October 10, 2006 at 5:59 am
Hmmm... I'm not absolutely sure about that. Subqueries tend to produce about the same behavior as UDF, but maybe not always. In any case, I'm trying to stay away from both if possible, and use JOINs wherever it works.
I don't know what your function does, but isn't there a chance to perform this calculation using join (one or several)? Could you post the UDF code?
October 10, 2006 at 6:10 am
It's no big deal - I already have a working query written, using subqueries. It's just that it's quite an ugly block of code to do a fairly simple task, and I was worried about maintenance. Briefly, it's a time utilisation report. Basically, for each employee it's executing one subquery to get available time, another to get time spent on jobs, then it's calculating a util % value based on these, and finally it also crops up in a where clause because we're only interested in high / low values. So the same subqueries are executed several times. I was hoping to replace this with some UDFs, even at the expense of performance, to make things more readable. What I don't understand is why SQL complains when I try to pass employee.[id] as a value.
I'm not going to worry too much as I have a query that works, at the end of the day. I could always replace with a cursor anyway, because then I can pass employee ID as a local variable - that works fine. I was simply curious as to why I'm not able to do it the way I wanted...
Often the way!
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply