How to create a ternary condition on a bit field in T-SQL

Question

I have a SQLExpress table that includes a bit field for storing TRUE/FALSE state.

Something like:

+----+---------+
| ID | IsAlive |
+----+---------+
| 1  |    1    |
| 2  |    0    |
| 3  |   NULL  |
| 4  |    1    |
+----+---------+

Using that table as our example, I want to create one Stored Procedure that will do any one of the following:

1. Retrieve all records.
2. Retrieve only the records with IsAlive=1.
3. Retrieve only the records with IsAlive=0 or NULL.

I am trying to think of how I can create my query without having to write IF/ELSE conditions - It seems to me there is a better/cleaner way than to do something like this:

-- The ternary logic...
-- 0 or NULL retrieves records where IsAlive = 0 or NULL
-- 1 retrieves records where IsAlive = 1
-- Otherwise return all records 

-- sproc .....

    @IsAlive tinyint = 2 -- Return all records by default

    AS
    BEGIN
      IF(@SentToNTService = 0 OR @SentToNTService = 1)
       BEGIN
        SELECT *
        FROM MyTable
        WHERE IsAlive = @IsAlive;
       END
    ELSE -- Lame redundancy 
       BEGIN
        SELECT *
        FROM MyTable
       END  
    END

Is there another way of creating the same results without having to create two different queries as I did above?

Answer 1

2 suggestions of how to do this:

Assuming your variable @isalive is declared as 'bit' as well (which is should be)

SELECT * FROM @t
WHERE @isalive is null or @isalive = coalesce(isalive, 0)

If you want to use a 'bit compare' solution that doesn't require @isalive to be 'bit' (it will work for bit as well as tinyint)

SELECT * FROM @t
WHERE coalesce((1-coalesce(isalive, 0)) ^ @isalive, 1) > 0

Second solution is for nerds like me. Some hardcore people may find it interesting (or at least amusing) as I think it offer the best possible performance (please, someone correct me if i am wrong). It is a powerful solution but hard to read.