Where clause with a conditional condition

Question

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 

Answer 1

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.