Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
msinca
Starting Member
3 Posts |
 Posted - 2010-08-11 : 12:52:57
|
| Hello. I'm helping a client migrate a legacy system and am writing some SQL Server 2008 R2 SPs for them. I'm having trouble with the following:Simplified Background:2 Tables (User, Document). User (id int, name varchar, groups varchar)1 Tom 50,70,902 Mary 30,50,803 Sam 10,50,70Document (id int, docname varchar, groups varchar)1 Financials 30,902 Real Estate 50,70,903 Employee List 10the 'groups' field is used for security purposes. a user only is allowed to see documents to the 'group' that he belongs to. however, users can belong to 1 or more groups and documents can be read by 1 or more groups.I need to write a SP that first pulls up the groups that a user belongs to (ex. Sam - 10,50,70) and then does a LIKE %X% to find which documents he can read (ex. Sam -> Real Estate and Employee List). Essentially, I'll need a statement that looks like:Select id FROM documentWHERE group like '%' + 10 + '%' OR group like '%' + 50 + '%' OR group like '%' + 70 + '%'I can't modify the table structure and use intermediary tables (which should have been done a long time ago). I can use temporary tables but am not sure how that would help. QUESTION: Is there any way of doing this without doing a dynamic query? I think I can figure it out that way but I try to make that a last resort. I know how to parse the CSV group strings but can't figure out how to incorporate that into a WHILE loop (with Union) or something like that.Any help would be much appreciated. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-08-11 : 12:58:53
|
| I would use Viskahs fnParseValues, which you can find on this site. It will turnn the strings into tables, which you can then join on.JimEveryday I learn something that somebody else already knew |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
msinca
Starting Member
3 Posts |
Posted - 2010-08-11 : 13:24:06
|
| Thanks for the replies. However, its not the parse that I have trouble with - its the WHERE statement. Consider another user record (4 Mark 20). In this case the statement will be:Select id FROM documentWHERE group like '%' + '20' + '%'How do you do a WHERE clause where it could be 1 statement or 20 statements (separated by OR's) depending on how many items I parse out of a user's group privileges? My only answer: build a dynamic query. Maybe thats the only answer here.Again, thanks for the help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-11 : 13:32:01
|
why? this wont suffice?SELECT DISTINCT n.docnameFROM(SELECT u.name,f.ValFROM User uCROSS APPLY dbo.ParseValues(u.groups,',')f)mJOIN(SELECT d.docname,f.ValFROM Document dCROSS APPLY dbo.ParseValues(d.groups,',')f)nON n.Val=m.ValWHERE m.name = @UserName @UserName is passed on user name------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
msinca
Starting Member
3 Posts |
Posted - 2010-08-11 : 14:20:02
|
| visakh16: that is IMPRESSIVE. Seems to work great. I had not previously worked with CROSS APPLY (and frankly, I'm not even 100% sure how it works at this moment but I will step through it carefully to understand it). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Hairy Parse Like Statement - Dynamic Query?
. My version is ParseValues. see