trying to find a way to use a derived table instead of a temp table that is looped upon

Question

trying to find a way to use a derived table instead of a temp table that is looped upon

Matt K-201518

SSC Rookie

Points: 49
More actions
February 8, 2005 at 9:48 am

#74859

I am trying to find a way to use a derived table instead of a temp table that is looped upon (avoiding cursors altogether).
background;
I have a UserRoles table with UserID and RoleID, both fields point to the UserID in a User table (a role is just a different type of user).
the User can have roles and the roles can have roles, so it supports nesting.
currently to get the users roles I do this...
DECLARE @user-id int, @SubLevel int
SET @user-id = 1
SET @SubLevel = 1
--make temp table to add roles as they are found
CREATE TABLE #Stack (RoleID int, SubLevel int)
--add first role (users id) to temp table
INSERT INTO #Stack
VALUES (@UserID, @SubLevel)
--if new roles found for last level, go another level...and limit it to first 100 levels
WHILE EXISTS (SELECT * FROM #Stack WHERE SubLevel = @SubLevel) AND @SubLevel < 100
BEGIN
    --add all roles found for current role to next level
    INSERT INTO #Stack
    SELECT ur.RoleID, @SubLevel+1
    FROM UserRoles ur
    WHERE EXISTS (SELECT * FROM #Stack WHERE #Stack.RoleID = ur.UserID AND #Stack.SubLevel <= @SubLevel)
    --increment to next level
    SET @SubLevel = @SubLevel + 1
END
select * from #Stack
drop table #Stack
Any help is greatly appreciated.
THANKS

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Nesting = hierarchy,

hierarchy cannot be defined using a single select statement unless you know that there is a last level limit in which case you make the appropriate number of left joins... but in this case I don't see one.

Maybe could you change the design of the db and split Roles and Users into two tables? Then maybe you could have a line for each users/roles (I mean one for each parent/child roles). that would allow you to make a single inner join bewteen the 2 tables and get the data you're after without using while loops or cursors.

Matt K-201518 SSC Rookie Points: 49 More actions · Answer 2

Thanks for your response.

Unfortuantely we do not know the level of nesting, we limit it to 100 but it is not fixed.

We use one table for Users and Roles because the SecurityRights are linked to it and these rights are inherited from each nested role back up to the user along with whatever rights the user themselves have. It seems overly complicated, but its what was required for the system.

I was thinking there might be some ultra cool way of doing it that i was just missing, but i'm thinking your right and its just not possible with this setup.

THANKS AGAIN...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Since you are talking about 100 levels of nesting you are probabely better off using your current setup even if it's not considered best pratice. Just make sure you don't use a recursive algorithm since you'd most likely bust the 32 nesting limit imposed by sql server... but the current code doesn't cause any problems as it stands.

tal mcmahon-202603 SSC-Addicted Points: 474 More actions · Answer 4

Hi,

You could build a User defined function that is recursive. I do not have a SQL server handy so i cannot give you the code.

here is a link that describes it:

http://www.extremeexperts.com/SQL/Articles/UDFFunctions.aspx

if I remember you will get an rerror when you compile it the first time because the UDF that you are refering to does not exists. (the one your making). I just save it with a blank function then do an Alter.

HTH

tal mcMahon

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

This doesn't help right now, but the recursive Common Table Expressions in Sql Server 2005 handle variable depth hierarchies really well. I guess deploying on a Beta platform isn't an option , huh ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

A function as the same nested level limit of 32 so it wouldn't do any good in this case.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 7

Hi Matt,

will this help you ?

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=152361</a?

I think it is a similar problem.

Regards,

Goce.

Matt K-201518 SSC Rookie Points: 49 More actions · Answer 8

Thanks for everyones responses.

As someone above pointed out, the option of using a recursive function has a limit of 32, so that is ruled out. And since we wouldn't want to deploy to Sql Server 2005 beta, that option is out (very cool that it will be there though).

So for now I will continue to use my existing (rather un-cool looking) stored procedure.