Where one of the values in a csv column is equal to one of the values in a csv passed in

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Where one of the values in a csv column is equal to one of the values in a csv passed in

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-30 : 08:28:20

Tony writes "I have looked at all of the csv related docs on this site but none of them has enabled me to do what I need.

Here is a psuedo-code example and some sample data and results of what I am looking for. I hope this makes sense. If not please let me know.

Psuedo-code SPROC example:

CREATE PROCEDURE GetTabs
(
@PageID int,
@ViewRoles nvarchar(500),
@EditRoles nvarchar(500)
)
AS
SELECT
TabID,
Title,
IsEditable
FROM
Tabs
WHERE
PageID = @PageID
AND
one of the values in AuthViewRoles = one of the values in @ViewRoles
ORDER BY
ViewOrder
GO

*Note: IsEditable should return True if one of the values in AuthEditRoles = one of the values in @EditRoles, otherwise it should return False

Sample Data:

Tabs Table:

TabID / PageID / ViewOrder / Title / AuthViewRoles / AuthEditRoles
1 / 1 / 1 / General / Manager;Acct;Sales;CustService; / Manager;Acct;Sales;CustService;
2 / 1 / 2 / Contacts / Manager;Acct;Sales;CustService; / Manager;Acct;Sales;CustService;
3 / 1 / 3 / Locations / Manager;Acct;Sales;CustService; / Manager;Acct;CustService;
4 / 2 / 1 / General / Manager;Acct;Sales;CustService; / Manager;Acct;Sales;CustService;
5 / 2 / 2 / Forums / Manager;Acct;Sales;CustService; / Manager;
6 / 1 / 5 / History / Manager;Acct;CustService; / Manager;Acct;
7 / 2 / 3 / Links / Manager;Acct;Sales;CustService; / CustService;
8 / 2 / 4 / FAQ / Manager;Acct;Sales;CustService; / CustService;
9 / 1 / 4 / Financial / Manager;Acct; / Acct;
10 / 3 / 1 / General / Manager;Acct;Sales;CustService; / Manager;

Sample Results:

Sample #1:
	@PageID = 1
	@ViewRoles = 'Acct;'
	@EditRoles = 'Acct;'
	
	Should Return:
	
	TabID	Title		IsEditable
	1	General		True
	2	Contacts	True
	3	Locations	True
	9	Financial	True
	6	History		True

Sample #2:
	@PageID = 1
	@ViewRoles = 'Sales;CustService;'
	@EditRoles = 'Sales;'
	
	Should Return:
	
	TabID	Title		IsEditable
	1	General		True
	2	Contacts	True
	3	Locations	False
	6	History		False

There you have it. I am hoping this can be done with one sproc and using no temp tables. I guess we will see.

Thanks for your help."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources