SQL Server Bitwise Processing like C# Enum Flags

Question

How can one use in SQL Server the processing of the Flags such as on enums in C#?

---

For example, how would one return a list of users that are part of a list or conditions like so:

ConditionAlpha = 2
ConditionBeta  = 4
ConditionGamma = 8

...

Then there will be users with some of these conditions against them like so:

User1: 6 (conditions Alpha and Beta)
User2: 4 (condition Beta)
User3: 14 (conditions Alpha, Beta and Gamma)

...

We want to be able to do a query where we say get all users with the first condition Alpha and in this scenario it would return users 1 and 3 even though they have other conditions as well.

Answer 1

The bitwise operator for checking whether a flag is set in SQL is &. The WHERE clause needs to evaluate to a BOOLEAN expression, like this:

create table #temp (id int, username varchar(20), flags int)

insert into #temp values
(1, 'User1', 6 /* (2 | 4) */),
(2, 'User2', 4),
(3, 'User3', 14 /* (2 | 4 | 8) */)

declare @ConditionOne int = 2

select *
from   #temp
where  flags & @ConditionOne <> 0

declare @ConditionTwo int = 4

select *
from   #temp
where  flags & @ConditionTwo <> 0

declare @ConditionThree int = 8

select *
from   #temp
where  flags & @ConditionThree <> 0

drop table #temp

These queries return the following resultsets:

id          username             flags
----------- -------------------- -----------
1           User1                6
3           User3                14

id          username             flags
----------- -------------------- -----------
1           User1                6
2           User2                4
3           User3                14

id          username             flags
----------- -------------------- -----------
3           User3                14