Search code examples
sql-servertable-functions

SQL Select fields of table value function

I have a table valued function declared so that I can return several values in one go. The values are calculated using DECLARE and Maths and Date functions.

The function is structured such that it only takes a 'logged date', and a priority for issues in a support system. I honestly thought that I'd be able to select as follows:

SELECT SupportCall.*, dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority).* FROM SupportCall

I've actually ended up with:

SELECT SupportCall.*,
    SLADays = (select SLADays from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority)),
    SLARecieved = (select SLAReceived from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority)),
    SLATarget = (select SLATarget from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority)),
    SLAHoursRemaining = (select SLAHoursRemaining from dbo.GetSLAStatus(SupportCall.createDate, SupportCall.priority))
From SupportCall

I can't see a possible join for an Apply (which I don't fully understand anyway).

Does anybody know whether the function calls with the same parameters will be executed once? If I'm not going to end up with lots of subqueries and function calls when the query runs, then I don't care, the code is actually quite tidy if not concise.

If there is a massive overhead, does anybody know how to select all columns from a table function of this kind (i.e. no keys, just several calculations on the same input data).

Thanks.

Mark

Solution

  • Can't you just do this:

    SELECT C.*,
    F.SLADays,
    F.SLAReceived,
    F.SLATarget,
    F.SLAHoursRemaining
From 
    SupportCall C
    cross apply dbo.GetSLAStatus(C.createDate, C.priority) F

    I hope you're function is an inline function (e.g has a single statement that starts returns table return (...) and does not have a defined result table)