SQL Server: Case with the statement "NOT IN"

Question

I am trying to do a CASE statement in SQL Server (stored procedure) where I am supposed to check if whether or not it should get some results from another table.

I just made this up to illustrate the example (not working!)

SELECT
    *
FROM
    cards
WHERE
    CardID = @CardID
    AND
    CardID = 
        CASE WHEN @AlreadyOnDeck = 1 THEN
            IN (
                    SELECT CardID FROM OnDeckTable WHERE CardID = @CardID
            )
        CASE WHEN @AlreadyOnDeck = 0 THEN
            NOT IN (
                    SELECT CardID FROM OnDeckTable WHERE CardID = @CardID
            )
        END

I need to make the case IN / NOT IN depending on @AlreadyOnDeck variable

Answer 1

Similar to Max's answer but uses CASE statements. (May produce a slightly better execution plan due to short circuiting of the outer CASE statement.)

SELECT
    *
FROM
    cards c
WHERE
    c.CardID = @CardID
    AND
    1 = (CASE WHEN @AlreadyOnDeck = 1 THEN 
                   (CASE WHEN EXISTS(select * OnDeckTable dt where dt.CardID = c.CardID) THEN 1 END)
              WHEN @AlreadyOnDeck = 0 THEN 
                   (CASE WHEN NOT EXISTS(select * from OnDeckTable dt where dt.CardID = c.CardID) THEN 1 END)       
        END)

Answer 2

I think this will work, but haven't tested (no test data provided).

SELECT
    *
FROM
    cards
WHERE
    CardID = @CardID
    AND
    (
        exists (select 1 from OnDeckTable where CardId = @CardId and @AlreadyOnDeck = 1)
        or not exists (select 1 from OnDeckTable where CardId = @CardId and @AlreadyOnDeck = 0)
    )