USP_NFG_GETSITES_UPDATEFROMXML

create procedure dbo.USP_NFG_GETSITES_UPDATEFROMXML
(
    @NFGID uniqueidentifier,  
    @XML xml,  
    @CHANGEAGENTID uniqueidentifier = null,  
    @CHANGEDATE datetime = null  
)
as
set nocount on;  

if @CHANGEAGENTID is null  
 exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output  

if @CHANGEDATE is null   
 set @CHANGEDATE = getdate()  

-- build a temporary table containing the values from the XML  

declare @TempTbl table (  
   [ID] uniqueidentifier,  
   [SITEID] uniqueidentifier)  

insert into @TempTbl select   
    [ID],  
    [SITEID]   
from dbo.UFN_FAFPROGRAM_GETSITES_FROMITEMLISTXML(@XML)  

update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');  

if @@Error <> 0  
 return 1;  

declare @contextCache varbinary(128);  
declare @e int;  

-- cache current context information   

set @contextCache = CONTEXT_INFO();  

-- set CONTEXT_INFO to @CHANGEAGENTID   

if not @CHANGEAGENTID is null  
 set CONTEXT_INFO @CHANGEAGENTID;  

-- delete any items that no longer exist in the XML table  

delete from dbo.[FAFNFGCampaignDisplaySite] where [FAFNFGCampaignDisplaySite].ID in   
 (select ID from dbo.UFN_FAFNATIONALFUNDRAISINGGROUP_GETSITES (@NFGID )  
 EXCEPT select ID from @TempTbl)   

select @e=@@error;  

-- reset CONTEXT_INFO to previous value   

if not @contextCache is null  
 set CONTEXT_INFO @contextCache;  

if @e <> 0  
 return 2;  

-- update the items that exist in the XML table and the db  

update [FAFNFGCampaignDisplaySite]  
  set [FAFNFGCampaignDisplaySite].[ID]=temp.[ID],  
        [FAFNFGCampaignDisplaySite].[SITEID]=temp.[SITEID],  
  [FAFNFGCampaignDisplaySite].CHANGEDBYID = @CHANGEAGENTID,  
  [FAFNFGCampaignDisplaySite].DATECHANGED = @CHANGEDATE  

 from dbo.[FAFNFGCampaignDisplaySite] inner join @TempTbl as [temp] on [FAFNFGCampaignDisplaySite].ID = [temp].ID  
 where ([FAFNFGCampaignDisplaySite].[ID]<>temp.[ID]) or   
        ([FAFNFGCampaignDisplaySite].[ID] is null and temp.[ID] is not null) or   
        ([FAFNFGCampaignDisplaySite].[ID] is not null and temp.[ID] is null) or   
        ([FAFNFGCampaignDisplaySite].[SITEID]<>temp.[SITEID]) or   
        ([FAFNFGCampaignDisplaySite].[SITEID] is null and temp.[SITEID] is not null) or   
        ([FAFNFGCampaignDisplaySite].[SITEID] is not null and temp.[SITEID] is null)  

if @@Error <> 0  
 return 3;   

-- insert new items  

insert into [FAFNFGCampaignDisplaySite]   
 ([NFGCAMPAIGNID],   
    [ID],  
    [SITEID],      
 ADDEDBYID,   
 CHANGEDBYID,   
 DATEADDED,   
 DATECHANGED)  
select @NFGID,   
    [ID],  
    [SITEID],   
 @CHANGEAGENTID,   
 @CHANGEAGENTID,   
 @CHANGEDATE,   
 @CHANGEDATE  
from @TempTbl as [temp]  
where not exists (select ID from dbo.[FAFNFGCampaignDisplaySite] as data where data.ID = [temp].ID)  

if @@Error <> 0  
 return 4;  

return 0;