I use SQL Server 2008 and have a stored procedure with one input parameter formatted as xml. This xml is a list of names, one word each without spaces.
For each of these names I want to check if they already exist in a table, if no then they should get added to the table, if yes then they should get updated there.
So far I have the part to add them if they don't exist yet which works as intended but I can't figure out how to realise the updating part.
Can someone here help me with this ?
Just for demonstration this would be the update part as a stand-alone (if I would have one input instead of the xml):
UPDATE RC_PermissionsUsers
SET ntid = @ntid,
departmentID = @departmentID,
role = @role
WHERE ntid = @ntid
The rest of my procedure for the insert part (working):
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN
INSERT INTO RC_PermissionsUsers
(
ntid,
departmentID,
[role]
)
SELECT ParamValues.ntid.value('.', 'varchar(255)'),
@departmentID,
@role
FROM @xmlUsers.nodes('/users/ntid') AS ParamValues(ntid)
WHERE NOT EXISTS
(
SELECT ntid
FROM RC_PermissionsUsers
WHERE ntid = ParamValues.ntid.value('.', 'varchar(255)')
)
END
COMMIT TRANSACTION;
END
Many thanks in advance, Tim.
No comments:
Post a Comment