Bets way to do this sql ?

[Gman]

Got a User that can have multiple roles.

Database looks like so:

User
------
UserID

UserRoles
-------------
UserID
RoleID

Roles
--------
RoleID


When I create or update a user I can set multiple roles e.g. NewsPoster, EventPoster, Administrator etc.

At the moment i'm getting the list of role ID's in a comma seperated list from the aspx page. But trying to work out the best way to write the sql to loop through the comma seperated list and insert and insert values into the UserRoles link table.

I can use the following

CREATE PROCEDURE unpack_with_union
                 @list      nvarchar(MAX),
                 @tbl       varchar(30),
                 @delimiter nchar(1) = N',' AS
DECLARE @sql nvarchar(MAX),
        @q1  char(1),
        @q2  char(2)
SELECT @q1 = char(39), @q2 = char(39) + char(39)
SELECT @sql = 'INSERT INTO ' + @tbl + ' SELECT ' +
               replace(replace(@list, @q1, @q2), @delimiter,
                       N' UNION ALL SELECT ')
--PRINT @sql
EXEC (@sql)

which will insert the list values but I need to include the user ID as well.
In the stored procedure I've got the userID as a variable but not sure how I can add this into the existing insert statement.

 

[PsychoDuck]

I think you want to turn your insert into something like the following:-

create table #user
(
	userid int,
	username varchar(100)
)

create table #roles
(
	roleid int,
	role varchar(100)
)

create table #userroles
(
	userid int,
	roleid int
)

insert into #user select 1, 'rob'
insert into #user select 2, 'fred'

insert into #roles select 1, 'admin'
insert into #roles select 2, 'dataentry'

select * from #userroles

insert into #userroles
select u.userid, r.roleid from #user u cross join #roles r
where u.userid = 1 and r.roleid in (2)

 

[Gman]

thanks will give it a go