T-SQL - Using AND condition only if a value from a

问题:

I want to add an AND condition in my query after the WHERE clause only if I find (a) value(s) from a list of predefined values, otherwise the condition should not be added.

The condition I want to add here is "AND Table2.fieldvalue = importantvalue" only when a parameter value is present in a list of (1001, 1002, 1003, 1004, 1005, 1006, 1007)

Also the parameter that comes in is a STRING with INT values comma separated but I have a user defined function to split and cast it into INT

SELECT field1,field2,field3....
from Table1 
join Table2 ON Table1.somefield = Table2.somefield
WHERE Table1.field1 = value 
AND Table2.field2 = value
AND ( CASE WHEN @parameter IN ( 1001, 1002, 1003, 1004, 1005, 1006, 1007) AND Table2.fieldvalue = importantvalue THEN 1
           ELSE 0
      END ) = 1 AND Table2.somethingelse    
GROUP BY blah,blah,blah..
HAVING blah,blah

回答1:

This looks odd at first read, but it works. I have this in a search SP to take into consideration only parameters with a non-NULL value. When the parameter is non-NULL, it is comma-separated string coming from the app.

WHERE ..................
AND (MyTable.MyColumn IN (SELECT * FROM dbo.func_SplitString(@Parameter, ',')) OR @Parameter IS NULL)

Note that dbo.func_SplitString returns a TABLE data type.

回答2:

You can use nested cases:

(CASE WHEN Table1.field3 IN ( 1001, 1002, 1003, 1004, 1005, 1006, 1007) 
    THEN (CASE WHEN Table2.fieldvalue = importantvalue THEN 1 ELSE 0 END)
    ELSE 1
END ) = 1

You can even make it single condition, but beware of the way NOT IN deals with nulls:

(
    Table2.fieldvalue = importantvalue OR 
    Table1.field3 NOT IN ( 1001, 1002, 1003, 1004, 1005, 1006, 1007) OR
    Table1.field3 IS NULL
)