spUpdateClientRoles

CREATE PROCEDURE [dbo].[spUpdateClientRoles]
            (
            @ClientRolesID int,
            @RecordIDs xml,
            @BBSystemID int
            )
            AS

            BEGIN

            DECLARE @BackOfficeRecordIDs TABLE (ID int) 

            DECLARE @idoc int
            EXEC sp_xml_preparedocument @idoc OUTPUT, @RecordIDs
            INSERT INTO @BackOfficeRecordIDs
            SELECT *
            FROM OPENXML (@idoc, '/ArrayOfInt/int', 2)
            WITH  ([ID] int '.')
            EXEC sp_xml_removedocument @idoc

            BEGIN TRANSACTION

            delete from UserRoles where ClientRolesID = @ClientRolesID and ManuallyAdded = 0

            INSERT INTO UserRoles (ClientUsersID, ClientRolesID, ManuallyAdded) 
            (select vwBOU.[Userid], @ClientRolesID, 0
            from vwBackOfficeLinkedUsers vwBOU 
            inner join @BackOfficeRecordIDs boids on boids.id = vwBOU.BackOfficeRecordID
            WHERE vwBOU.BackOfficeSystemID = @BBSystemID)

            DECLARE @UpdateDate datetime
            SET @UpdateDate = getutcdate()

            update ClientUsers SET MembershipRefreshedOn = @UpdateDate
            WHERE ClientUsers.ID IN (SELECT ClientUsersID FROM UserRoles WHERE ClientRolesID = @ClientRolesID)

            update ClientRoles set RefreshedDate  = @UpdateDate
            where ID = @ClientRolesID

            COMMIT TRANSACTION

            END