Repost: just in case i can get an answer

Hello all I have a situation so far I have been unable to find a solution
I have 3 tables like so
CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NULL ,
[FullName] [varchar] (50) NULL ,
[UserName] [varchar] (50) NULL ,
[Password] [varchar] (50) NULL ,
[Email] [varchar] (50) NULL ,
[CreationDate] [smalldatetime] NULL ,
[LastLogon] [smalldatetime] NULL
)
CREATE TABLE [dbo].[Users2SecurityRights] (
[Users2SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[SecurityRightID] [int] NULL ,
[AssignedDate] [smalldatetime] NULL ,
[AssignedBy] [int] NULL
)
CREATE TABLE [dbo].[SecurityRights] (
[SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
[SecurityRightName] [varchar] (50) NULL ,
[SecurityRightDescription] [varchar] (100) NULL
)
Now I have a stored procedure that will return a string with all the user
Security rights like so (Administrator, Ag, Ed, Cus)
CREATE PROCEDURE ReturnSecurityRights
(
@.UserID INT
)
as
BEGIN
DECLARE @.Count INT
DECLARE @.CountOfRows INT
DECLARE @.SecurityRights VARCHAR(100)
DECLARE @.SecurityRightsHolder VARCHAR(100)
DECLARE MCursor CURSOR
LOCAL
STATIC
FOR (SELECT SecurityRights.SecurityRightName FROM Users2SecurityRights
INNER JOIN SecurityRights ON Users2SecurityRights.SecurityRightID =
SecurityRights.SecurityRightID
WHERE USERID = @.UserID)
OPEN MCursor
SET @.Count = 1
SET @.CountOfRows = @.@.CURSOR_ROWS
SET @.SecurityRights = ''
WHILE @.Count <= @.CountofRows
BEGIN
FETCH MCursor
INTO @.SecurityRightsHolder
SET @.SecurityRights = @.SecurityRightsHolder + ',' + @.SecurityRights
SET @.Count = @.Count + 1
END
SELECT @.SecurityRights
CLOSE MCursor
DEALLOCATE MCursor
END
I know, I know why am i using cursors...
What I want is to query the Users Table and return a result set where the
return of the SP(ReturnSecurityRights) is inline with the fields of the
Users Table
In essence i want to be able to do this
SELECT *, (EXEC ReturnSecurityRights UserID) FROM Users
I understand that I need to use a select within a subquery, just not sure
how i can accomplish what i need.
I can do this programically through Code but I really would like SQL Server
to process this
Thanks All, Hope I was clear in what I want to accomplish, and thank you to
all that will help me find the way
LenLeonard Danao wrote:
> Hello all I have a situation so far I have been unable to find a
> solution I have 3 tables like so
> CREATE TABLE [dbo].[Users] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerID] [int] NULL ,
> [FullName] [varchar] (50) NULL ,
> [UserName] [varchar] (50) NULL ,
> [Password] [varchar] (50) NULL ,
> [Email] [varchar] (50) NULL ,
> [CreationDate] [smalldatetime] NULL ,
> [LastLogon] [smalldatetime] NULL
> )
> CREATE TABLE [dbo].[Users2SecurityRights] (
> [Users2SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NULL ,
> [SecurityRightID] [int] NULL ,
> [AssignedDate] [smalldatetime] NULL ,
> [AssignedBy] [int] NULL
> )
> CREATE TABLE [dbo].[SecurityRights] (
> [SecurityRightID] [int] IDENTITY (1, 1) NOT NULL ,
> [SecurityRightName] [varchar] (50) NULL ,
> [SecurityRightDescription] [varchar] (100) NULL
> )
> Now I have a stored procedure that will return a string with all the
> user Security rights like so (Administrator, Ag, Ed, Cus)
> CREATE PROCEDURE ReturnSecurityRights
> (
> @.UserID INT
> )
> as
> BEGIN
> DECLARE @.Count INT
> DECLARE @.CountOfRows INT
> DECLARE @.SecurityRights VARCHAR(100)
> DECLARE @.SecurityRightsHolder VARCHAR(100)
> DECLARE MCursor CURSOR
> LOCAL
> STATIC
> FOR (SELECT SecurityRights.SecurityRightName FROM
> Users2SecurityRights INNER JOIN SecurityRights ON
> Users2SecurityRights.SecurityRightID = SecurityRights.SecurityRightID
> WHERE USERID = @.UserID)
> OPEN MCursor
> SET @.Count = 1
> SET @.CountOfRows = @.@.CURSOR_ROWS
> SET @.SecurityRights = ''
> WHILE @.Count <= @.CountofRows
> BEGIN
> FETCH MCursor
> INTO @.SecurityRightsHolder
> SET @.SecurityRights = @.SecurityRightsHolder + ',' + @.SecurityRights
> SET @.Count = @.Count + 1
> END
> SELECT @.SecurityRights
> CLOSE MCursor
> DEALLOCATE MCursor
> END
>
> I know, I know why am i using cursors...
> What I want is to query the Users Table and return a result set where
> the return of the SP(ReturnSecurityRights) is inline with the fields
> of the Users Table
> In essence i want to be able to do this
> SELECT *, (EXEC ReturnSecurityRights UserID) FROM Users
> I understand that I need to use a select within a subquery, just not
> sure how i can accomplish what i need.
> I can do this programically through Code but I really would like SQL
> Server to process this
http://www.sqlteam.com/item.asp?ItemID=2955
http://www.windowsitpro.com/SQLServ...5608/15608.html
http://www.stephenforte.net/owdasbl...>
15d6d813eeb8
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks :)
*** Sent via Developersdex http://www.examnotes.net ***