Is there a better way to write this gross SQL?

So I’m creating a query for a report that could have several optional filters. I’ve only included client and station here to keep it simple. Each of these options could be an include or an exclude and could contain NULL, 1, or multiple values. So I split the varchar into a table before joining it to the query.

This test takes about 15 minutes to execute, which… just won’t do :p Is there a better way? We have similar queries written with dynamic sql, and I was trying to avoid that, but maybe there’s no way around it for this?

DECLARE   
@ClientsInc VARCHAR(10) = 'ABCD, EFGH', 
@ClientsExc VARCHAR(10) = NULL,     
@StationsInc VARCHAR(10) = NULL, 
@StationsExc VARCHAR(10) = 'SomeStation'
    
SELECT *
INTO #ClientsInc
FROM dbo.StringSplit(@ClientsInc, ',')
SELECT *
INTO #ClientsExc
FROM dbo.StringSplit(@ClientsExc, ',')
  
SELECT *
INTO #StationsInc
FROM dbo.StringSplit(@StationsInc, ',')
SELECT *
INTO #StationsExc
FROM dbo.StringSplit(@StationsExc, ',')    

SELECT [some stuff]
FROM media_order mo
LEFT JOIN #ClientsInc cInc WITH(NOLOCK) ON cInc.Value = mo.client_code
LEFT JOIN #ClientsExc cExc WITH(NOLOCK) ON cExc.Value = mo.client_code
LEFT JOIN #StationsInc sInc WITH(NOLOCK) ON sInc.Value = mo.station_name
LEFT JOIN #StationsExc sExc WITH(NOLOCK) ON sExc.Value = mo.station_name
WHERE ((@ClientsInc IS NOT NULL AND cInc.Value IS NOT NULL)
         OR (@ClientsExc IS NOT NULL AND cExc.Value IS NULL)
       )    
   AND ((@StationsInc IS NOT NULL AND sInc.Value IS NOT NULL)
         OR (@StationsExc IS NOT NULL AND sExc.Value IS NULL)
        )

Answer

First of all, I always tend to mention Erland Sommarskog’s Dynamic Search Conditions in such cases.

However, you already seem to be aware of the two options: one is dynamic SQL. The other is usually the old trick and (@var is null or @var=respective_column). This trick, however, works only for one value per variable.

Your solution indeed seems to work for multiple values. But in my opinion, you are trying too hard to avoid dynamic sql. Your requirements are complex enough to guarantee it. And remember, usually, dynamic sql is harder for you to code, but easier for the server in complex cases – and this one certainly is. Making a performance guess is always risky, but I would guess an improvement in this case.