Geek Answers Handbook

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

A simple request may not be possible, but I know there are some smart people :)

Given the boolean parameter, I want to define a where clause to either limit the specific output of the column or do nothing.

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

where column = 1

given parameter @bit = 0, this will be the result:

where column = 1 or 0

i.e. do not affect / show all the results (the column is a bit).

I don't need dynamic sql — I can decide to fix this in the code, but I just wondered if there was any kind of smart magic that would make it more neat and simple.

Whether there is a? I am using sql server.

greetings: D

+4
source share
8 answers

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

+7
source 
 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 blog post for a comparison of the performance of one WHERE and UNION ALL WHERE :

+4
source 
 where column BETWEEN @bit AND 1
+3
source 
 select * from MyTable where (@bit = 0 OR MyColumn = 1)
0
source 
 select ... from [table] where @bit = 0 or (column = @bit)
0
source

I came up with a different answer and felt stupid when I saw a consensus answer. So, just for yucks, compare the two using my own database. I really don't know if they are really comparable, but my execution plans give a slight advantage to my painful answer:

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

I understand that indexes, table size, etc. may affect this.
Also, I realized that you probably can't compare a bit with -1 ... Just thought I'd share.

0
source

try it

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

this works regardless of the data type of the column (for example, there may be a row). If it were, of course, it would be a different default value (not 0)

0
source

WHERE column> = @bit

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

0
source

More articles:


All Articles