T-sql - Using And Condition Only If A Value From A List Is Present

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 con

Solution 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.

Solution 2:

You can use nested cases:

(CASEWHEN Table1.field3 IN ( 1001, 1002, 1003, 1004, 1005, 1006, 1007) 
    THEN (CASEWHEN Table2.fieldvalue = importantvalue THEN1ELSE0END)
    ELSE1END ) =1

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

(Table2.fieldvalue=importantvalueORTable1.field3NOTIN(1001,1002,1003,1004,1005,1006,1007)ORTable1.field3ISNULL)