Select Statement with Temp Column driven by Bitmask

Question

I have a role system setup that checks to see if bits are set for specific roles.

Each user has a specific assigned that is stored in our database and gives them access to different parts of the program. What I'm trying to do is pull every user from the database and add a column beside the role number with what parts they have access to.

I can do this with a case statement but that will only find one of the permissions unless I break down all the combinations of cases.

Database Setup:

Name | Role |
Jon  | 77   |

Permissions:

@ONE       = 1
@TWO       = 2
@THREE     = 4
@FOUR      = 8
@FIVE      = 16
@SIX       = 32
@SEVEN     = 64
@EIGHT     = 128
@NINE      = 256
@TEN       = 1024
@ELEVEN    = 2048
@TWELVE    = 32768
@THIRTEEN  = 65471

So for Jon I want to see:

Name | Role | Roles
Jon  | 77   | One Three Four Seven

Forrest · Accepted Answer · 2016-12-19T19:28:16.787

SQL Server has bitwise operations that you can use. The & symbol provides an AND operation comparing bit to bit. Source: https://msdn.microsoft.com/en-us/library/ms174965.aspx

Here is code (not extended to all role cases) that should work:

CREATE TABLE #t
([Name] varchar(30),
[Role] int)

INSERT INTO #t
VALUES ('Jon',77)

SELECT [Name],
    CASE 
    WHEN [Role] & 1 = 1 THEN 'One '
    ELSE ''
    END
    +
    CASE 
    WHEN [Role] & 2 >= 2 THEN 'Two '
    ELSE ''
    END
    +
    CASE 
    WHEN [Role] & 4 >= 4 THEN 'Three '
    ELSE ''
    END
    +
    CASE 
    WHEN [Role] & 8 >= 8 THEN 'Four '
    ELSE ''
    END
FROM #t

The comparison only occurs where binary digits exist for both numbers, so we only have to care about the leading 1 of the result, thus the inequality.

Select Statement with Temp Column driven by Bitmask

1 Answers1