Stored procedure help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stored procedure help

Author

Topic

birixiote
Starting Member

1 Post

Posted - 2008-10-23 : 00:02:01

I have a table which has the following columns -
GroupID - UniqueID
GroupName - nvarchar
GroupType - nvarchar (the possible values are Private or Public).

I have an asp.net page where a user can view the list of groups. The list will contain any public group and only the private groups the user is member of. On the page load of the page i retrieve the membership of the user (private and public).

I am thinking of passing in the user's membership collection as a parameter to a stored procedure which will generate the list (which will contain all the public and only the private group he is a member of).

So this is what I am thinking, i will create a temp table which will store the user's membership collection. Then i run a select on the group table, that selects all the groups. Then compares the rows against temp table, which will return all the public groups and private groups that are in the temp table.

Can anybody help me with the stored procedure? I would greatly appreciate your help.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 00:06:57

where will user's member details be held?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-23 : 00:57:16

try something like this


DECLARE @UserId INT
SELECT @UserId = 1

SELECT GroupId, GroupName
FROM Groups
WHERE GroupType = 'Public'
UNION
SELECT G.GroupId, G.GroupName
FROM Groups G
INNER JOIN User_Groups UG ON UG.GroupId = G.GroupId
WHERE G.GroupType = 'Private'
	AND UG.UserId = @UserId

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 01:15:48

[code]SELECT GroupId, GroupName
FROM Groups
LEFT JOIN User_Groups UG
ON UG.GroupId = G.GroupId
AND UG.UserId = @UserId
WHERE G.GroupType = 'Public'
OR UG.UserId IS NOT NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources