USP_EVENTSPONSOR_GETBENEFITS_UPDATEFROMXML

CREATE procedure dbo.USP_EVENTSPONSOR_GETBENEFITS_UPDATEFROMXML
(  
    @EVENTSPONSORID uniqueidentifier,  
    @XML xml,  
    @CHANGEAGENTID uniqueidentifier = null,  
    @CHANGEDATE datetime = null  
)  
as
begin
    -- do work


    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 (  
       [BASECURRENCYID] uniqueidentifier,  
       [BENEFITID] uniqueidentifier, 
       [DETAILS] nvarchar(255),  
       [ID] uniqueidentifier,  
       [QUANTITY] int,  
       [TOTALVALUE] money,  
       [UNITVALUE] money)  

    insert into @TempTbl select   
        [BASECURRENCYID],  
        [BENEFITID],  
        [DETAILS],  
        [ID],  
        [QUANTITY],  
        [TOTALVALUE],  
        [UNITVALUE]   
    from dbo.UFN_EVENTSPONSOR_GETBENEFITS_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;  
  declare @EVENTSPONSORSHIPTYPEID uniqueidentifier;

    -- 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.[SPONSORBENEFITEXTENSION]
    where [SPONSORBENEFITEXTENSION].ID in   
    (select ID from dbo.UFN_EVENTSPONSOR_GETBENEFITS(@EVENTSPONSORID) 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;

  set @EVENTSPONSORSHIPTYPEID = dbo.UFN_EVENTSPONSOR_GETEVENTSPONSORSHIPTYPEID(@EVENTSPONSORID)

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

    update SPONSORBENEFITEXTENSION  
    set
        [BASECURRENCYID] = temp.[BASECURRENCYID],  
        [BENEFITID] = temp.[BENEFITID],  
        [DETAILS]=temp.[DETAILS],  
        [ID]=temp.[ID],  
        [QUANTITY]=temp.[QUANTITY],  
        [TOTALVALUE]=temp.[TOTALVALUE],  
        [UNITVALUE]=temp.[UNITVALUE],  
        CHANGEDBYID = @CHANGEAGENTID,  
        DATECHANGED = @CHANGEDATE  

    from dbo.SPONSORBENEFITEXTENSION SB inner join @TempTbl as [temp] on SB.ID = [temp].ID  
    where (SB.[BASECURRENCYID]<>temp.[BASECURRENCYID]) or   
        (SB.[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or   
        (SB.[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or   
        (SB.[BENEFITID]<>temp.[BENEFITID]) or   
        (SB.[BENEFITID] is null and temp.[BENEFITID] is not null) or   
        (SB.[BENEFITID] is not null and temp.[BENEFITID] is null) or   
        (SB.[DETAILS]<>temp.[DETAILS]) or   
        (SB.[DETAILS] is null and temp.[DETAILS] is not null) or   
        (SB.[DETAILS] is not null and temp.[DETAILS] is null) or   
        (SB.[ID]<>temp.[ID]) or   
        (SB.[ID] is null and temp.[ID] is not null) or   
        (SB.[ID] is not null and temp.[ID] is null) or   
        (SB.[QUANTITY]<>temp.[QUANTITY]) or   
        (SB.[QUANTITY] is null and temp.[QUANTITY] is not null) or   
        (SB.[QUANTITY] is not null and temp.[QUANTITY] is null) or   
        (SB.[TOTALVALUE]<>temp.[TOTALVALUE]) or   
        (SB.[TOTALVALUE] is null and temp.[TOTALVALUE] is not null) or   
        (SB.[TOTALVALUE] is not null and temp.[TOTALVALUE] is null) or   
        (SB.[UNITVALUE]<>temp.[UNITVALUE]) or   
        (SB.[UNITVALUE] is null and temp.[UNITVALUE] is not null) or   
        (SB.[UNITVALUE] is not null and temp.[UNITVALUE] is null)  

    if @@Error <> 0 return 3;   

    -- insert new items  

    insert into SPONSORBENEFITEXTENSION   
    (
        SPONSORID,   
        [BASECURRENCYID],  
        [BENEFITID],
    [EVENTSPONSORSHIPTYPEID],
        [DETAILS],  
        [ID],  
        [QUANTITY],  
        [TOTALVALUE],  
        [UNITVALUE],      
        ADDEDBYID,  
        CHANGEDBYID,   
        DATEADDED,   
        DATECHANGED
    )
    select 
        @EVENTSPONSORID,   
        [BASECURRENCYID],  
        [BENEFITID],  
    @EVENTSPONSORSHIPTYPEID,
        [DETAILS],  
        [ID],  
        [QUANTITY],  
        [TOTALVALUE],  
        [UNITVALUE],   
        @CHANGEAGENTID,   
        @CHANGEAGENTID,   
        @CHANGEDATE,   
        @CHANGEDATE  
    from @TempTbl as [temp]  
    where not exists (select ID from dbo.SPONSORBENEFITEXTENSION as data where data.ID = [temp].ID)
  and BENEFITID is not null

    if @@Error <> 0 return 4;  

    return 0;  

end