If..Else or Case in the WHERE clause - something else

Question

I read q & a's loads here, but none of them fit the bill for what I'm trying to achieve. I don’t even know if this is possible! Any help / suggestions would be greatly appreciated.

I am trying to create a conditional sentence WHEREbased on

CustID (int) = @CustomerID 

If @CustomerID = 0, then I want the result to be WHERE CustID > 0(i.e. return all customers), otherwise I want only certain customersCustID = @CustomerID

Is it possible?

Answer 1

Just do WHERE (@CustomerID = 0 OR CustID = @CustomerID)

Answer 2

something like that?

SELECT * 
FROM YOURTABLE
WHERE 
( CASE 
     WHEN @CustomerID = 0 THEN CustID 
     ELSE 1 
  END
) > 0
AND 
( CASE 
     WHEN @CustomerID <> 0 THEN @CustomerID 
     ELSE CUSTID 
  END
) = CUSTID

Answer 3

IF-ELSE sql CASE DECODE ( , oracle): Ex :

if (x == 1) then y; else if (x == 2) then z; end if

CASE: select case x when 1 then y when 2 then z end example from dual;

:   select decode(x,1,y,2,z) example from dual;