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

Question

I need help writing a conditional where clause. here is my situation:

I have a bit value that determines what rows to return in a select statement. If the value is true, I need to return rows where the import_id column is not null, if false, then I want the rows where the import_id column is null.

My attempt at such a query (below) does not seem to work, what is the best way to accomplish this?

DECLARE @imported BIT

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

Thanks.

Answer 1

Change the AND to OR

DECLARE @imported BIT

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

Answer 2

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)
    ^^^

Answer 3

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 BIT

IF @imported = 1
  SELECT id, import_id, name
  FROM Foo
  WHERE import_id IS NOT NULL
ELSE
  SELECT id, import_id, name
  FROM Foo
  WHERE import_id IS NULL