sql query - true => true, false => true or false

Question

Hi,

Simple query, possibly impossible but I know there are some clever people out there :)

Given a boolean parameter, I wish to define my where clause to either limit a certain column's output - or do nothing.

So, given parameter @bit = 1 this would be the result:

where column = 1

given parameter @bit = 0 this would be the result:

where column = 1 or 0

i.e. have no effect/show all results (column is a bit field)

I'm not wanting dynamic sql - I can settle for fixing this in code but I just wondered if there's some clever magic that would make the above neat and simple.

Is there? I'm using sql server.

cheers :D

Answer 1

SELECT  *
FROM    mytable
WHERE   column = 1 OR @bit = 0

If you have an index on column1, this one will be more efficient:

SELECT  *
FROM    mytable
WHERE   column = 1 AND @bit = 1
UNION ALL
SELECT  *
FROM    mytable
WHERE   @bit = 0

See this article in my blog for performance comparison of a single WHERE condition vs. UNION ALL:

• IN with a comma separated list: SQL Server

Answer 2

select *
from   MyTable
where (@bit = 0 OR MyColumn = 1)

Answer 3

select ...
from [table]
where @bit = 0 or (column = @bit)

Answer 4

The answer column = 1 or @bit = 0 works if column may only be 0 or 1. If column may be any value you want: column = 1 or @bit = 0 and column = 0.

Answer 5

where column BETWEEN @bit AND 1

Answer 6

I had come up with a different answer and felt dumb when seeing the consensus answer. So, just for yucks, compared the two using my own database. I don't really know if they are really comparable, but my execution plans give a slight advantage to my goofy answer:

select *
from MyTable
where column <> case @bit when 1 then 0 else -1 end

I realize indices, table size, etc. can affect this.
Also, realized you probably can't compare a bit to a -1...
Just thought I'd share.

Answer 7

try this

 select ...
 from table
 where column = case when @bit = 0 then 0 else column end

this works no matter what the datatype of column is (could even be a string, for example). If it were, of course, it would be a different default value (not 0)

Answer 8

WHERE column >= @bit

However, this only works for > 0 values in a numeric column. @bit will be implicitly cast to int, smallint etc because of data type precedence.