CASE statement/ conditional where clause

Question

I know a conditional where clause isn't exactly supported by SQL Server, but wondering how I can accomplish this.  Based on the value of another table I need to apply different conditions in my where clause like so:

            and Data.Location IN (
                     CASE [Rule].[Specialty]
                       WHEN 'excld sss' THEN (SELECT Distinct [Location] 
                                                             FROM [Location_Filter] 
                                                             WHERE Description not like '%SSS doors%') 
WHEN 'sss only' THEN (SELECT Distinct [Location] 
                                   FROM [Location_Filter] 
                                   WHERE Description like '%SSS doors%') 
ELSE ( Data.Location) 
 END
                     )

Answer 1

E.g.

SELECT  *
FROM    sys.tables T
WHERE   ( [Rule].Specialty = 'excld sss'
          AND Data.Location IN ( SELECT Location
                                 FROM   Location_Filter
                                 WHERE  [Description] NOT LIKE '%SSS doors%' )
        )
        OR ( [Rule].Specialty = 'sss only'
             AND Data.Location IN ( SELECT  Location
                                    FROM    Location_Filter
                                    WHERE   [Description] LIKE '%SSS doors%' )
           )
        OR NOT [Rule].Specialty IN ( 'excld sss', 'sss only' );
                           

But this is a case where I would consider using a temporary table or table variable to store the locations. Cause the LIKE predicate is not sargable. Thus no index is used here. This maybe a performance bottle neck.

Answer 2

I think it would be this

FROM Data d
....
...
LEFT JOIN [Location_Filter] l
ON l.[Location] = d.[Location]
AND l.Description like '%SSS doors%'
WHERE ...
AND (([Rule].[Specialty] ='excld sss' AND l.[Location] IS NULL)
OR ([Rule].[Specialty] = 'sss only' AND l.[Location] IS NOT NULL))

---

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

E.g.

SELECT  *
FROM    sys.tables T
WHERE   ( [Rule].Specialty = 'excld sss'
          AND Data.Location IN ( SELECT Location
                                 FROM   Location_Filter
                                 WHERE  [Description] NOT LIKE '%SSS doors%' )
        )
        OR ( [Rule].Specialty = 'sss only'
             AND Data.Location IN ( SELECT  Location
                                    FROM    Location_Filter
                                    WHERE   [Description] LIKE '%SSS doors%' )
           )
        OR NOT [Rule].Specialty IN ( 'excld sss', 'sss only' );
                           

But this is a case where I would consider using a temporary table or table variable to store the locations. Cause the LIKE predicate is not sargable. Thus no index is used here. This maybe a performance bottle neck.

Perfect!  I cannot thank you enough for this and responding so quickly!  Much appreciated!

Answer 4

Many thanks as well!