CASE in WHERE clause?

[gateshosting]

I thought this syntax was correct, but I can't figure out why it won't work. Maybe it just is not supported?

CREATE PROCEDURE spTEST @includepaid bit AS

SELECT FIELD FROM TABLE
WHERE
  SOLD = 1
  AND CUSTOMER = 2

  CASE
    WHEN @includepaid = 1 THEN AND [Paid] = 1
  END

This is not my exact query, but you get the drift. All my logic is correct in my SP. I want to be able to turn PAID to include or not include...

Thanks,

Michael C. Gate

[almaz]

I thought this syntax was correct, but I can't figure out why it won't work. Maybe it just is not supported?

...

This is not my exact query, but you get the drift. All my logic is correct in my SP. I want to be able to turn PAID to include or not include...

Thanks,

Michael C. Gate

CASE statement can only return a value, not a condition string. So you should rewrite your WHERE clause like following (assuming that Paid column doesn't allow nulls):

WHERE
  SOLD = 1
  AND CUSTOMER = 2
  AND [Paid] =
    CASE
      WHEN @includepaid = 1 THEN 1
      ELSE [Paid]
    END

[gateshosting]

Thanks... sorry it was a topic already covered. I found that later doing some searches.

I understand now how it only returns a value. I am thinking like VB or C#, and not SQL. I am new to the logic of SQL. Thanks,

Michael C. Gates