How do I insert rows if their values appear in a list but are not in the table and delete them if they do not appear in the list but are in the table?

Question

Recently, I was put in charge of cleaning up a SQL Server 2008 database and code for website, and am having trouble coming up with an elegant solution to the following problem. On the website, there are users that have different roles, and each role has a number of permissions. The admin on the website can enable or disable permissions for a given role.

Currently, the database is NOT normalized, and the tables are set up like so:

Roles:

RoleID | RoleName | Permissions
  1    | Admin    | 1;2;3;4;5;etc.
  2    | User     | 4;23;54;etc.
  3    | Person   | 54;2;3;45;etc.

Permissions:

PermissionID | PermissionName
    1        | Random Name
    2        | You get the idea
    3        | etc.

To update a roles permissions, they basically iterate through every checked box in the codebehind, generate the "permission string" and update the row in a singular call.

I want to normalize the database as follows:

Roles:

RoleID | RoleName 
  1    | Admin    
  2    | User     
  3    | Person  

Permissions:

PermissionID | PermissionName
    1        | Random Name
    2        | You get the idea
    3        | etc.

RolePermissions:

rpID | RoleID | PermissionID
 1   |   1    |      1
 2   |   1    |      2
 3   |   1    |      20
 4   |   2    |      5
 5   |   2    |      2

The problem is the user may want to change 20-30 permissions at a time. The only way I can think of doing it is deleting every permission for that role and then adding each Role-Permission relation one by one. Normally this wouldn't be an issue, but our database is stored on a separate server from our code, so the network latency from making each of the 30+ commands alone would be a killer. I also know that in this case, the user should be updating the roles fairly infrequently, but I am trying to switch the entire database to be normalized, so this problem would occur elsewhere on more commonly used pages.

I am wondering if there is an elegant way in one or two SQL commands to insert the new Role-Permission relations into the RolePermissions table if they do not exist, and delete any RolePermissions that no longer exist. The code behind for this is C#, so I can pretty easily generate any SQL command string that is required. Any suggestions?

If more information is required, I will supply it.

Answer 1

If you're using a fairly recent client library that supports table valued paremeters, you can use them to send a list of data to a stored procedure.

create type PermissionList as table (
    PermissionID int not null
);
Go

create proc UpdateRolePermissions
    @roleID int, 
    @permissionList PermissionList readonly
as
with t as (
    select
        *
    from
        dbo.RolePermissions
    where
        RoleId = @roleID
)
merge
    t
using
    @permissionList s
on
    t.PermissionID = s.PermissionID
when not matched by source then 
    delete
when not matched by target then
    insert (roleID, permissionID)
    values (@roleID, s.PermissionID)
;
go

If there's more data and you need to update something when matched, you can also add a when matched then update clause

Example of using table valued parameter from C#

Example SQLFiddle