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.

Lieven Keersmaekers · Accepted Answer · 2011-09-01 06:23:29Z

19

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

edited Sep 1, 2011 at 6:23

answered Mar 9, 2009 at 16:04

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sfossen · Accepted Answer · 2009-03-09 16:04:56Z

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

answered Mar 9, 2009 at 16:04

sfossen

4,77826 silver badges18 bronze badges

Comments

Tom H · Accepted Answer · 2010-07-06 15:16:03Z

1

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

edited Jul 6, 2010 at 15:16

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

answered Mar 9, 2009 at 16:37

Amy B

110k21 gold badges140 silver badges191 bronze badges

2 Comments

mmattax Over a year ago

Thanks for the suggestion, but the query I gave was just an example, it is much more complicated, so splitting them like this is not feasible at the moment.

Amy B Over a year ago

More complexity -> more reason to break up this into the correct number of queries. stackoverflow.com/questions/266697/…

Collectives™ on Stack Overflow

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

3 Answers 3

Decomposing your original statement

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Decomposing your original statement

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related