Bitwise OR of all columns in result set

Question

Bitwise OR of all columns in result set

whatispunk

Old Hand

Points: 356
More actions
June 11, 2008 at 8:43 am

#186680

I am trying to write a UDF that will return a tinyint of the bitwise ORing of X number of tinyints retrieved from a table.
The tinyints are integer representations of access permissions specified by a 4 bit binary string.
So if someone has several different permissions on a resource, they will have more than one entry in my permissions tables. I need to get the combination of all of their access entries.
The UDF recieves 2 parameters: @ResourceID int, @PrincipalsXml xml
@ResourceID specifies a single resource they are trying to access.
@PrincipalsXml specifies the Group/Role/User associated with the request (can be multiple)
An example of the xml is:
<Principals>
<Principal ID="1" />
<Principal ID="2" />
<Principal ID="3" />
</Principals>
The query I currently retrieves a result set containing all of the access columns (tinyints) but now I am kind of stuck on how to OR them all into one result.
I'm considering using a recursive CTE, although I've used CTEs never a recursive one. I'm hoping there's a better option.
Can I create my own aggregate UDF or is there an existing one that could do this?
Thanks!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Mike - you XML got "whacked" - you may need to replace the brackets with their HTML encoding &lt; and &gt; for it to show.

that being said - I don't think you need a recursive CTE at all. If you simply want to OR all of the principals together - use some logic like the following:

--test date setup only - this is NOT part of what you'd need to emulate

create table #matt(num int)

insert #matt

select 1 union all

select 2 union all

select 4 union all

select 8 union all

select 16 union all

select 32 union all

select 5 union all

select 15 union all

select 42

--end od test data

declare @fun int

set @fun=0;

select @fun=@fun|num

from #matt

select @fun

--clean up the test data

drop table #matt

You can test this - but the @fun variable should be holding the OR'ed results of all of the rows.

If you need to repeat this for multiple users in a single recordset - you could set this up as a UDF if you wish and then invoke it against a DISTINCT query.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

whatispunk Old Hand Points: 356 More actions · Answer 2

Many thanks! That worked out very well. At first it was just returning NULL and I couldn't see why, then it dawned on me that if you don't set your result variable to 0 before you start ORing it with other values you will always get NULL. So to anyone reading this in the future, make sure you set your result to 0, its critical.

Oh, and about the XML. Jeez, you are just such a go-getter. I had this thread posted for all of 5 seconds and you were already reading/answering it. I managed to fix the xml after I saw the initial post attempt (I really should preview my posts first eh?).

Thanks again for your speedy reply.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Glad it helped!

And I've been bitten many times by the XML thing (the site trying to protect itself from injection/little nasties, etc...), so I thought I'd pass that trick along. good that you didn't even need it..:)

Anyway - thanks for the feedback.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

whatispunk Old Hand Points: 356 More actions · Answer 4

I don't frequent these forums much, mostly because I don't have much to offer in the way of answers. I don't do a ton of SQL.

I do however lurk around the asp.net forums quite frequently.

Over there we have a way to mark a post as "The Answer". I don't see any functionality like that here. Am I missing it? If not, I think it is a crucial element to any support forum and you might want to make the recommendation to add this feature. Can't be that difficult.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Mike Sigsworth (6/11/2008)
I don't frequent these forums much, mostly because I don't have much to offer in the way of answers. I don't do a ton of SQL.
I do however lurk around the asp.net forums quite frequently.
Over there we have a way to mark a post as "The Answer". I don't see any functionality like that here. Am I missing it? If not, I think it is a crucial element to any support forum and you might want to make the recommendation to add this feature. Can't be that difficult.

You're not dreaming - it doesn't exist.

I've had the same thought as well, and unless I'm severely mistaken - that's been passed to Steve as a suggestion as well. They're working on getting around a few interesting "features" they found buying the built-in package the site is run on.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?