SQL: Can I negate a condition in a where clause?

Question

I want to check if a boolean is true, then decide in the WHERE clause what condition to use.

Say the boolean variable is @checkbool:

SELECT *
FROM TableA A
WHERE
    --if @checkbool is true, run this
    A.Id = 123

    --if @checkbool is false, run this
    A.Id <> 123

Is there a way to negate a condition? Like in C++ you can do if !(condition).

If not, what is the best way to solve this problem?

Thank you!

Answer 1

SQL's equivalent of ! in C is NOT. However, in your case you want something else: you need to build a condition that decides between the two choices based on the value of @checkbool, like this:

SELECT *
FROM TableA A
WHERE (    (@checkbool) AND (A.Id =  123))
   OR ((NOT @checkbool) AND (A.Id <> 123))

Answer 2

Here is one solution:

IF @Checkbool = 1
     SELECT * FROM Table A WHERE A.Id = 123
ELSE
     SELECT * FROM Table A WHERE A.Id <> 123

Here is another using just the WHERE Clause:

SELECT * 
FROM Table A 
WHERE
     (@Checkbool = 1 AND A.Id = 123)
     OR
     (@Checkbool = 0 AND A.Id <> 123)

Everything you put in the where clause needs to be in the form of an expression. Thus, the solution in this case is to write the condition in the form of an expression.

Hope this helps. :)