Trying to resolve three conditions for a bit column-CodePudding

Good Morning,

Seems like this shouldn't be hard to do but my TSQL knowledge is lacking. How do I resolve three conditions for a bit column? This is what I have but of course the compiler doesn't like the OR in the CASE.

To be clear a in the case of All I want both zero and one column values. Null will be excluded.

MyTable

Id  Name                LockedState    Deleted
12  Doe, John               0             1
14  Doe, Johnny             0             1
15  Fischer, Julia          1             1
16  Hemsworth, Christopher  0             1
17  Getty, Kristyn          1             1



SELECT *
FROM MyTable
WHERE Deleted = @Deleted
AND LockState = CASE
    WHEN @Assigned = 'ALL' THEN 0 OR 1
    WHEN @Assigned = 'Assigned' THEN 1
    WHEN @Assigned = 'UnAssigned' THEN 0
END

Desired Output for 'All'

12  Doe, John               0             1
14  Doe, Johnny             0             1
15  Fischer, Julia          1             1
16  Hemsworth, Christopher  0             1
17  Getty, Kristyn          1             1

Desired Output for 'Assigned'

15  Fischer, Julia          1             1
17  Getty, Kristyn          1             1

Desired Output for 'Unassigned'

12  Doe, John               0             1
14  Doe, Johnny             0             1
16  Hemsworth, Christopher  0             1

CodePudding user response：

You can use something like this:

SELECT *
FROM MyTable
WHERE Deleted = @Deleted
AND (@Assigned = 'Assigned' AND LockState = 1
    OR @Assigned = 'UnAssigned' AND LockState = 0
    OR @Assigned = 'ALL')

or like this:

SELECT *
FROM MyTable
WHERE Deleted = @Deleted
AND LockState = CASE
    WHEN @Assigned = 'ALL' THEN LockState
    WHEN @Assigned = 'Assigned' THEN 1
    WHEN @Assigned = 'UnAssigned' THEN 0
END