Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Where clause switch case on 2 boolean variables

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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)
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading