SQL - "IF" in Where Clause

Question

I'm trying to write a stored procedure that will have 6 bit value flags as parameters and a couple of other values.

The pseudo sql I want to write is something like:

SELECT *
FROM   theTable
WHERE  
       IF @flagA = 1 THEN theTable.A = 1
       IF @flagB = 1 THEN theTable.B = 1
       IF @flagC = 1 THEN theTable.CValue = @cValue
       etc

Any ideas how I can do this in SQL or am I best reverting to building the SQL in C# (where this SP will be called from)?

Answer 1

SELECT * 
FROM   theTable 
WHERE   
   (@flagA = 0 or (@flagA = 1 AND theTable.A = 1 ))
    and (@flagB = 0 or (@flagB = 1 AND theTable.B = 1 ))
    and (@flagC = 0 or (@flagC = 1 AND theTable.CValue = @cValue ))

Note: I am assuming your bit flags are non-nullable. If that is not the case, you will need to use ISNULL.