Help with parsing assigned groups and roles

[nicenjguy]

This is my problem

Table 1 is a list of reports that have the following columns

Report ID, Report Name, Groups Allowed, Roles Allowed
1 Report 1 Clerk, Mgr User, PowerUser

Table 2 is a list of users

UserID, User Name, Group, Role
u1 John Doe Clerk User
u2 Jane Doe Mgr PowerUser

How do i loop thru the reports table 1 and select the reports based on Group and Role in the user table 2?

I have already got a function to parse the columns
as follows


CREATE FUNCTION ParseString2Table
(
@SourceString varchar(100)
)

RETURNS @retTable TABLE
(
TextValue varchar(10)
)

AS

BEGIN
declare @string varchar(500)
set @string = @SourceString
declare @pos int
declare @piece varchar(10)

/*-- Need to tack a delimiter onto the end of the input string if one doesn't exist*/
if right(rtrim(@string),1) <> ','
set @string = @string + ','

set @pos = patindex('%,%' , @string)
while @pos <> 0
begin
set @piece = left(@string, @pos - 1)

/*-- You have a piece of data, so insert it, print it, do whatever you want to with it.*/
INSERT INTO @retTable VALUES( cast(@piece as varchar(10)))

set @string = stuff(@string, 1, @pos, '')
set @pos = patindex('%,%' , @string)
end

RETURN

END

I need some way of looping thru the reports table without using a cursor and
checking the user permissions

Help Please...

[iburyak]

Not sure if I understand correctly values in following columns

Groups Allowed, Roles Allowed



Nicenjgirl

[nicenjguy]

Not sure if I understand correctly values in following columns

Groups Allowed, Roles Allowed



Nicenjgirl

Well I guess I should not have used User & Mgr as examples.
Let's say 2 different departments - Manufacturing & Finishing

so the Groups Allowed column is varchar (1000) and would be
"MAN, FIN, DELIVERY"
and the Roles Allowed columnd is also varchar(1000) and would be something like
"User, PowerUser"

[iburyak]

I think I got it without your last reply.

See what I did.

1. Create tables and populate with data:

[PHP]create table table1(ReportID varchar(10), ReportName varchar(10), GroupsAllowed varchar(100), RolesAllowed varchar(100))
insert into table1 values ('1 Report', '1 Clerk', 'Mgr User', 'PowerUser')
insert into table1 values ('2 Report', '1 Clerk', 'User', 'PowerUser User')


Create table table2(UserID varchar(10), UserName varchar(20), [Group] varchar(100), Role varchar(20))
insert into table2 values ('u1', 'John Doe', 'Clerk', 'User')
insert into table2 values ('u2', 'Jane Doe', 'Mgr', 'PowerUser')[/PHP]


2. Create Function

[PHP]CREATE FUNCTION fnSplit(@String nvarchar(1000), @Delimiter char(1), @SearchFor varchar(100))
RETURNS varchar(100)
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @OriginalString varchar(4000)

SELECT @OriginalString = @String
SELECT @INDEX = 1
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
IF @SLICE = @SearchFor
BEGIN
RETURN @SLICE
END

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN NULL
END [/PHP]

3. Run query:

[PHP]select *
from table1 a
join table2 b on dbo.fnSplit(a.RolesAllowed, ' ', b.role) = b.role[/PHP]


Good Luck.

Hope it works.
I tested and it did work for me.

[iburyak]

I used space for delimiter like in your first example but you can change it to comma if you wish.

Good Luck again.


VeryNiceNJGirl.... :)

[nicenjguy]

I used space for delimiter like in your first example but you can change it to comma if you wish.

Good Luck again.


VeryNiceNJGirl.... :)

Thanks a ton!!

NiceNJGirl.. You are the best!

[iburyak]

[font=Verdana][size=2]To tell you the truth I never wrote queries like that and was amazed that it worked... :) [/size][/font]
[font=Verdana][size=2]You don't need to loop through your table any more.[/size][/font]
[font=Verdana][size=2][/size][/font]
[font=Verdana][size=2][/size][/font]
[font=Verdana][size=2]It was a good one even thou I disapprove multiple values in one column. Poor design period.[/size][/font]
[font=Verdana][size=2] [/size][/font]
[font=Verdana][size=2]Good Luck.[/size][/font]

[iburyak]

What was it?

I will try again to post my message:

To tell you the truth I never wrote queries like that and was amazed that it worked... :)
You don't need to loop through your table any more.


It was a good one even thou I disapprove multiple values in one column. Poor design period.

Good Luck.

[nicenjguy]

What was it?

I will try again to post my message:

To tell you the truth I never wrote queries like that and was amazed that it worked... :)
You don't need to loop through your table any more.


It was a good one even thou I disapprove multiple values in one column. Poor design period.

Good Luck.

Dear VeryNiceNJGirl,

I know it is poor design. Guess I could add a couple of satellite tables.
Maybe you have encountered this situation before and have a data model handy?

[iburyak]

You just need extra tables for Reports

[PHP]Create table ReportRoles(
ReportID datatypehere,
Role datatypehere)

Create table ReportGroups(
ReportID datatypehere,
Group datatypehere)[/PHP]

The same should be done for users.
This way you would be able to join reports, users and its rights and groups.

Hope it is understandable.

Irina.

[nicenjguy]

You just need extra tables for Reports

[PHP]Create table ReportRoles(
ReportID datatypehere,
Role datatypehere)

Create table ReportGroups(
ReportID datatypehere,
Group datatypehere)[/PHP]

The same should be done for users.
This way you would be able to join reports, users and its rights and groups.

Hope it is understandable.

Irina.

Yes it is and thanks again. You have been very helpful.

Kenny