Conditionally modify query based on parameter

Question

I have this query (something like a case statement which I can use and fix it)

select *
from mytable
where 1=1 
and (isNull(ID, 0) = 0 OR UtilityID IN (9,40))

I also want to add another statement

select *
from mytable
where 1=1 
and UtilityID NOT IN (9,40)

Everything is happening in a procedure, so want to use a variable like declare @something so if that is passed as 1, use the first statement and the if 0 is passed, use the latter one.

the second statment is just after the and of the first statement, modified above — Seb
– Seb, Commented Aug 1, 2019 at 22:06

Caius Jard · Accepted Answer · 2019-08-01 22:46:02Z

1

While I appreciate the genius in Dale's answer I find this more readable:

IF @something = 0 
BEGIN
  select *
  from mytable
  where ID IS NULL OR ID = 0 OR UtilityID IN (9,40);
END
IF @something = 1
BEGIN
  select *
  from mytable
  where UtilityID NOT IN (9,40);
END

It's procedure code, so use IF to direct the control flow. Also expanded and simplified your where clauses

answered Aug 1, 2019 at 22:46

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

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

4 Comments

Seb Over a year ago

query is quite big, so using it two times makes no sense

Serguei Over a year ago

@Seb, consider creating a view or table-valued function from the large query to avoid repetition, then you can do something like: IF @something = 0 SELECT * FROM myview WHERE {ConditionA} ELSE SELECT * FROM myview WHERE {ConditionB}. In general the IF/ELSE approach will yield more efficient query plans than WHERE (@something = 0 AND {ConditionA}) OR (@something = 1 AND {ConditionB}).

Caius Jard Over a year ago

@seb to be fair, you did say "I have this query" and not "I have an enormous query but I've contrived this small example for this question" - we can only answer questions based on what you tell us :) I second the recommendation of creating a view /TVF - it gives you a single place to modify the logic of the code and might be reusable elsewhere too

SMor Over a year ago

Erland discusses different techniques for dynamic search conditions.

Dale K · Accepted Answer · 2019-08-01 23:43:47Z

I think I understand your logic, ignoring the 1=1 (which does nothing) you want to only allow id = 0 when @something = 1. This should do it:

declare @something bit = 0;

declare @mytable table (ID int, UtilityID int);

insert into @mytable (ID, UtilityID)
  select 0, 1 union all
  select 1, 2 union all
  select 2, 9 union all
  select 3, 40;

select *
from @mytable
where (
  (@something = 1 and (isnull(ID, 0) = 0 or UtilityID in (9,40)))
  or (@something = 0 and (UtilityID not in (9,40)))
);

A more performant approach for a larger query could be:

select *
from @mytable
where (@something = 1 and (isnull(ID, 0) = 0 or UtilityID in (9,40)))
union all
select *
from @mytable
where (@something = 0 and (UtilityID not in (9,40)));

PS: Hopefully your ID cannot ever by null - it should have a constraint on it.

Collectives™ on Stack Overflow

Conditionally modify query based on parameter

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related