How to avoid 2 select queries based on IF Condition

Question

Is it possible to avoid writing two select queries and use some case statement or logic to combine in one query.

If The Flag is true then student records are shown whose percentage is > 2 else show everything.

DECLARE @StudentPercentageFlag BIT = 1


IF(@StudentPercentageFlag = 1)
BEGIN
 SELECT * FROM STUDENT WHERE STUDENTPERCENTAGE > 2
END
ELSE
BEGIN
    SELECT * FROM STUDENT 
END

Don't. Your original query with the IF is the right way to do it. It provides the optimizer with simple and clear query, without the added logic, which will probably perform orders of magnitude better when the data sets grow large. **Remember - One query for one task!** The logic regarding which query to run is a business rule, not a data query, and should be separated. HTH. — SQLRaptor, Aug 08 '19 at 15:58
yes but in the stored procedure we are duplicating same query again and again — user1030181, Aug 08 '19 at 16:06

score 2 · Accepted Answer · answered Aug 08 '19 at 15:54

2

You could extend conditions:

DECLARE @StudentPercentageFlag BIT = 1;

SELECT * 
FROM STUDENT 
WHERE (STUDENTPERCENTAGE > 2 AND @StudentPercentageFlag = 1)
   OR (@StudentPercentageFlag IS NULL)
   OR (@StudentPercentageFlag <> 1);

answered Aug 08 '19 at 15:54

Lukasz Szozda

162,964
23
234
275

@user1030181 `if you have multiple mandatory conditions` you should ask new question then. It is working for the case defined in question. I guess you are searching for [kitchen sink problem](https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example) – Lukasz Szozda Aug 08 '19 at 16:06
No worries i fixed it it was something at my end. Thanks for this query. appreciate it – user1030181 Aug 08 '19 at 16:10

How to avoid 2 select queries based on IF Condition

1 Answers1