Where clause switch case on 2 boolean variables-CodePudding

Looking for a query which executes where clauses based on boolean variables.

Something like:

select
    COUNT(col1)
from
    table1
where
    (@var1 is not null and col1 > @var1)
    and (@var2 is not null and col1 <= @var2);

Note the above query doesn't do what I need. I basically want:

If @var1 == NULL and @var2 == NULL THEN true
If @var1 == NULL and @var2 != NULL THEN col1 <= @var2
If @var1 != NULL and @var2 == NULL THEN col1 > @var1
If @var1 != NULL and @var2 != NULL THEN col1 > @var1 && col1 <= @var2

CodePudding user response：

Seems like you gave up half way? Just continue as you started.

WHERE
    (@var1 IS NULL AND @var2 AND NULL)
    OR (@var1 IS NULL AND @var2 IS NOT NULL AND col2 <= @var2)
    OR (@var1 IS NOT NULL AND @var2 IS NULL AND col1 > @var1)
    OR (@var1 IS NOT NULL AND @var2 IS NOT NULL AND col1 > @var1 AND col2 <= @var2)

CodePudding user response：

Hope this should work

where    ( 1= case when @var1 is  null and @var2 is  null then 1 else 0 end )
      OR ( case when  @var1 is null and @var2 is not null then col2<=@var2 
          end)
      OR ( case when @var1 is not null and @var2 is null then col1>@var1 end)
      OR ( case when @var1 is not null and @var2 is not null then col1>@var1 
           and col2<=@var2 end)

CodePudding user response：

Just make subtle subtle change to your original query:

where (@var1 is null or col1 >  @var1)
  and (@var2 is null or col1 <= @var2)

It handles all four cases just the way you described.