CODEWITHSUNDEEP
sqlsql-servert-sql
mmattax
mmattax

Reputation: 27670

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

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.

Upvotes: 11

Views: 40572

Answers (3)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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)

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

Upvotes: 19

Amy B
Amy B

Reputation: 110091

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

Upvotes: 1

sfossen
sfossen

Reputation: 4778

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

Upvotes: 2

Related Questions