Dhx66PW9

1UPDATE  RC_PermissionsUsers
2SET     ntid = @ntid,
3        departmentID = @departmentID,
4        role = @role
5WHERE   ntid = @ntid
6	
7BEGIN
8
9    SET NOCOUNT ON; 
10
11    BEGIN TRANSACTION;
12
13        BEGIN       
14            INSERT INTO RC_PermissionsUsers
15            (
16                            ntid,
17                            departmentID,
18                            [role]
19            )
20            SELECT          ParamValues.ntid.value('.', 'varchar(255)'),
21                            @departmentID,
22                            @role
23            FROM            @xmlUsers.nodes('/users/ntid') AS ParamValues(ntid)
24            WHERE NOT EXISTS 
25            (
26                SELECT      ntid
27                FROM        RC_PermissionsUsers
28                WHERE       ntid = ParamValues.ntid.value('.', 'varchar(255)')
29            )               
30
31        END
32
33    COMMIT TRANSACTION;  
34END
35	
36BEGIN
37
38    SET NOCOUNT ON; 
39
40    BEGIN TRANSACTION;
41
42        BEGIN    
43
44            IF object_id('tempdb.dbo.#tmpList') IS NOT null DROP TABLE #tmpList
45            create table #tmpList(
46                id                      varchar(255),
47                departmentID            int,
48                role                    int
49            )
50
51            insert into #tmpList
52            SELECT          ParamValues.ntid.value('.', 'varchar(255)'),
53                            @departmentID,
54                            @role
55            FROM            @xmlUsers.nodes('/users/ntid') AS ParamValues(ntid)
56
57            MERGE  RC_PermissionsUsers as PU
58            USING ( select id, departmentID, role from #tmpList) T
59                    ON PU.ntid = T.id
60            WHEN MATCHED THEN 
61                UPDATE SET departmentId = T.departmentID,
62                           role = T.role
63            WHEN NOT MATCHED THEN
64                INSERT ( ntid, departmentID, role)  
65                   VALUES ( T.id, T.departmentID, T.role)           
66
67
68        END
69
70    COMMIT TRANSACTION;  
71END