SQL HELP - Conditional where clause based on a BIT variable - SQL Server

Change the AND to OR

DECLARE @imported BITSELECT id, import_id, name FROM Foo WHERE    (@imported = 1 AND import_id IS NOT NULL)    OR (@imported = 0 AND import_is IS NULL)

Decomposing your original statement

you have essentially written

    @imported = 1     AND import_id IS NOT NULL    AND @imported = 0     AND import_is IS NULL

wich is equivalent to

    @imported = 1 AND @imported = 0     AND import_id IS NOT NULL AND import_is IS NULL

what results in two pair of clauses that completely negate each other

I think you meant

SELECT id, import_id, name FROM Foo WHERE    (@imported = 1 AND import_id IS NOT NULL)    OR (@imported = 0 AND import_is IS NULL)    ^^^

Your query would require an OR to select between the different filters.It's better for the optimizer if you use separate queries in this case. Yes, code redundancy is bad, but to the optimizer these are radically different (and not redundant) queries.

DECLARE @imported BITIF @imported = 1  SELECT id, import_id, name  FROM Foo  WHERE import_id IS NOT NULLELSE  SELECT id, import_id, name  FROM Foo  WHERE import_id IS NULL