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.

 

[Gman]

thanks will give it a go