CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Abner P
Abner P

Reputation: 53

Where clause with a conditional condition

Case: I have a stored procedure in where I got all the information of a table. I have 2 parameters in order to set the Where clause but one of those could be 0.

Question: How do I do a Case When or an If in my Where clause depending on my parameter value? I want to apply the where clause only if the value is different from 0 if is 0 I don't want to do it.

Code:

@ID_ORDER,
@ID_SUPPLIER 

Select *
From Orders ord
where @ID_SUPPLIER = ord.ID_SUPPLIER
AND CASE WHEN @ID_ORDER = 0 THEN ord.ID_ORDER = @ID_ORDER END

Upvotes: 3

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You don't. You just use and and or:

select *
from Orders ord
where ord.ID_SUPPLIER = @ID_SUPPLIER) and
      (ord.ID_ORDER = @ID_ORDER or @ID_ORDER = 0);

Note that the logic you are attempting is backwards. This only applies the filter on id_order when the value is not 0.

Upvotes: 6

Related Questions