Conditionally include a line in a T-SQL WHERE clause

Question

Honestly, I've gone over other questions posted here that mention conditional where clauses and I'm not seeing any answers that seem to work for my situation. If one does, please point me to it!

In a stored procedure called by an SSRS report, the user can select one of two values in a dropdown - say "Yes" or "No" and that goes into the variable @color.

In the WHERE clause of the sproc I need to filter differently depending on the user's selection.

So, if @color = 'Yes', then I need to have a line in the WHERE clause that reads something like tb1.somecolumn = 'BLUE'. But if @color = 'No' then I don't need that line in the where clause at all (tb1.somecolumn can be any value).

How do I conditionally include a line in the WHERE clause?

Possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — EzLo, Feb 21 '19 at 15:25

score 1 · Answer 1 · answered Feb 21 '19 at 15:14

1

Try following condition

WHERE (@color = 'Yes' AND tb1.somecolumn = 'BLUE') 
   OR (@color = 'No'  AND tb1.somecolumn = tb1.somecolumn)

answered Feb 21 '19 at 15:14

S.Jose

216
1
7

score 1 · Accepted Answer · answered Feb 21 '19 at 15:19

Include the check for @color in the WHERE as follows.

WHERE (@color = 'No')
OR    (@color = 'Yes' AND tb1.somecolumn = 'BLUE')

Explanation:

If the value of @color is 'No', then all rows are returned.
If the value of @color is 'Yes', then only the 'BLUE' rows are returned.

Conditionally include a line in a T-SQL WHERE clause

2 Answers2