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.

Answer 2

This will do what you want:

    SELECT *
    FROM MyTable
    WHERE COALESCE(IsAlive, 0) = COALESCE(@IsAlive, COALESCE(IsAlive, 0))

Based on the value of @IsAlive:

1. If NULL, then will return everything (because the condition is always true)
2. If 1, then will return those rows with IsAlive = 1
3. If 0, then will return those rows with IsAlive = 0 or NULL

COALESCE is a function that returns it's first argument, unless it's NULL, in which case it returns its second argument.

So the LHS returns 0 if IsAlive is NULL or 0 and 1 if IsAlive is 1. The RHS returns the same when the stored procedure argument @IsAlive is NULL and just returns the @IsAlive argument otherwise.

EDIT: This assumed that @IsAlive is BIT. In the case of tinyint you can add a case statement:

    SELECT *
    FROM MyTable
    WHERE COALESCE(IsAlive, 0) = CASE @IsAlive
                                    WHEN 0 THEN 0
                                    WHEN 1 THEN 1
                                    ELSE COALESCE(IsAlive, 0)
                                 END

Answer 3

try this:

SELECT * FROM MyTable WHERE ISNULL (IsAlive, 0) = ISNULL (@IsAlive, 0)
UNION
SELECT * FROM MyTable WHERE ISNULL (@IsAlive, 0) > 1

Answer 4

This isnt exact, but pretty close to what you can do:

SELECT *        
FROM MyTable        
WHERE CASE @IsAlive
      WHEN 0 THEN IsAlive = @IsAlive
      WHEN 1 THEN IsAlive = @IsAlive
      ELSE 1=1 --dummy true value, when null or anything else
END

Answer 5

Something like this should also work.

SELECT *        
FROM MyTable        
WHERE (@IsAlive = 0 and IsAlive=0)
OR (@IsAlive =1 and IsAlive =1)
OR (@IsAlive is null)