spAddUpdate_GivingHistoryUsedFields

          CREATE PROCEDURE [dbo].[spAddUpdate_GivingHistoryUsedFields]
        (
            @PayloadXML XML,
            @SiteContentID int
        )
        AS
        BEGIN

        BEGIN TRY
        BEGIN TRANSACTION spAddUpdate_GHFields;

        -- Delete fields that are no longer used
        DELETE FROM GivingHistoryUsedFields
        WHERE SiteContentID = @SiteContentID And FieldID NOT IN 
        (SELECT Tbl.Col.value('@FieldID', 'INT') 
            FROM @PayloadXml.nodes('//GivingHistoryColumn') Tbl(Col)  
            WHERE SiteContentID = @SiteContentID)

        -- Update fields that are left
        UPDATE GivingHistoryUsedFields SET
            DisplayOrder = Tbl.Col.value('@DisplayOrder', 'INT') 
        FROM GivingHistoryUsedFields gh
        INNER JOIN @PayloadXml.nodes('//GivingHistoryColumn') Tbl(Col) ON Tbl.Col.value('@FieldID', 'INT') = gh.FieldID
        WHERE gh.SiteContentID = @SiteContentID

        -- Insert any new fields
        INSERT INTO GivingHistoryUsedFields(SiteContentID, FieldID, DisplayOrder)
        (
        SELECT @SiteContentID, Tbl.Col.value('@FieldID', 'INT'), Tbl.Col.value('@DisplayOrder', 'INT')
        FROM @PayloadXml.nodes('//GivingHistoryColumn') Tbl(Col)  
        WHERE Tbl.Col.value('@FieldID', 'INT') NOT IN (SELECT FieldID FROM GivingHistoryUsedFields WHERE SiteContentID = @SiteContentID)
        )

        COMMIT TRANSACTION spAddUpdate_GHFields

        END TRY
        BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
        END CATCH

        END