How do I force SQL Server to execute a query in a particular order

Question

I have the following query

    
DECLARE @userId INT  
DECLARE @siteId INT

SET @siteId = -1  
SET @userId = 1828  

SELECT  a.id AS alertId,  
        a.location_id,  
        a.alert_type_id,  
        a.event_id,  
        a.user_id,  
        a.site_id,  
        a.accepted_by  
FROM    alerts AS a    
JOIN    alert_types AS ats ON a.alert_type_id = ats.id 
JOIN    events AS tr ON a.event_id = tr.event_id 
WHERE   tr.end_Time IS null
AND     tr.status_id = 0
AND     ats.code = 'E'
AND     a.site_id in (SELECT * FROM dbo.udf_get_event_sitelist(@siteId, @userId))

This query takes between 5 and 17 seconds to run, however under many circumstances the function dbo.udf_get_event_sitelist(@siteId, @userId) returns no rows, so the query will not find any data.

How can I force SQL Server to execute the user defined function first. I appreciate that I could rewrite the query into a stored procedure and perform the sub-select first, however I would like to do it in a single SQL statement if possible.

Answer 1

make the "FROM" table the results set of the function and join the other tables to it

DECLARE @userId INT  
DECLARE @siteId INT

SET @siteId = -1  
SET @userId = 1828  

SELECT  a.id AS alertId,  
        a.location_id,  
        a.alert_type_id,  
        a.event_id,  
        a.user_id,  
        a.site_id,  
        a.accepted_by  
FROM    (SELECT * FROM dbo.udf_get_event_sitelist(@siteId, @userId)) dt
JOIN    alerts AS a ON dt.site_id=a.site_id
JOIN    alert_types AS ats ON a.alert_type_id = ats.id 
JOIN    events AS tr ON a.event_id = tr.event_id 
WHERE   tr.end_Time IS null
AND     tr.status_id = 0
AND     ats.code = 'E'

Answer 2

you could select the results of udf_get_event_sitelist into a table variable and only proceed with the big query if @@rowcount > 0