SQL Server : combine SELECT and related UDF results

Question

I failed to google this scenario: I would like to insert new rows into (time slot) table, for some records (selected by WHERE clause) and add 3 columns as a result of User-Defined-Function (which calculates a free slot date, start and end time). This has to work, even if the UDF returns more than one row.

Based on Microsoft's suggestion about using UDF:

SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);

I came up with this concept:

INSERT INTO PlanTimeSlots (........................)
    SELECT 
        PRJ.ID as RID,
        GST.SlotDate as SlotDate,
        GST.SlotStart as TimeStart,
        GST.SlotEnd as TimeEnd,
        PRJ.WPGroupID as WPGroupID,
        45 as Priority
    FROM 
        PlanRJ as PRJ
    LEFT JOIN 
        (SELECT 
             SlotDate, SlotStart, SlotEnd
         FROM 
             dbo.GetSuitableTimeSlot(PRJ.ID, PRJ.WPGroupID, 
                                     PRJ.DateReqBy, PRJ.DurationMin)) AS GST ON GST.JID = PRJ.ID
    WHERE 
        ........;

So I redundantly pass an RID to the UDF, which is returned as GST.JID, so there's a key to join UDFs result set to the main select.

Is this OK, or is there a better solution? It will work with hundreds to thousands entries and I'm not sure if this concept could perform well.

Manisha Sharma Manisha Sharma · Accepted Answer · 2017-09-04T11:48:18

1. query result will depend upon your where condition also. 

2. If you want to get all the records from left query then use left join otherwise change it to INNER join 
3. Treat your user defined function as other table no need for select statement.

    INSERT INTO PlanTimeSlots (........................)
        SELECT 
            PRJ.ID as RID,
            GST.SlotDate as SlotDate,
            GST.SlotStart as TimeStart,
            GST.SlotEnd as TimeEnd,
            PRJ.WPGroupID as WPGroupID,
            45 as Priority
        FROM 
            PlanRJ as PRJ
        INNER JOIN 
            dbo.GetSuitableTimeSlot(PRJ.ID, PRJ.WPGroupID, 
                                         PRJ.DateReqBy, PRJ.DurationMin) AS GST ON GST.JID = PRJ.ID
        WHERE 
            ........;

SQL Server : combine SELECT and related UDF results

1 Answers