There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?)
I have 6 optional arguments to a stored procedure that are used to filter a certain value of a query. The previous developer did something like this:
IF @var1 IS NULL AND ..... select ... else select ... where value in (@var1, ...)
I didn't particularly like this. (The query is quite massive), so I decided to opt for something like this:
(We had the results in a temp table anyway)
IF @var IS NOT NULL OR ... delete from #temp where value not in (@var1,...)
But I realized that this won't work.
The only thing I can think to do is to create another temp table that will hold only the not-null values in @var1, etc. (Either using if statements or deleting where is NULL) and then doing a join delete on this table.
Is there a better way?