USP_WPSEARCHHISTORY_GETCONSTITUENTS_UPDATEFROMXML

/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:09:33 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_WPSEARCHHISTORY_GETCONSTITUENTS_UPDATEFROMXML 
(
@WPSEARCHHISTORYID uniqueidentifier,
@XML xml
)

as

set nocount on;

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

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

insert into @TempTbl select 
    [CONSTITUENTID],
    [ID] 
from dbo.UFN_WPSEARCHHISTORY_GETCONSTITUENTS_FROMITEMLISTXML(@XML)

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

if @@Error <> 0
    return 1;

declare @e int;

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

delete from dbo.[WPSEARCHHISTORYSELECTEDCONSTITUENT] where [WPSEARCHHISTORYSELECTEDCONSTITUENT].ID in 
    (select ID from dbo.UFN_WPSEARCHHISTORY_GETCONSTITUENTS
    (
        @WPSEARCHHISTORYID
    )
    EXCEPT select ID from @TempTbl)    

select @e=@@error;

if @e <> 0
    return 2;

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

update [WPSEARCHHISTORYSELECTEDCONSTITUENT]
        set [WPSEARCHHISTORYSELECTEDCONSTITUENT].[CONSTITUENTID]=temp.[CONSTITUENTID],
        [WPSEARCHHISTORYSELECTEDCONSTITUENT].[ID]=temp.[ID]

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

if @@Error <> 0
    return 3;    

-- insert new items

insert into [WPSEARCHHISTORYSELECTEDCONSTITUENT] 
    ([WPSEARCHHISTORYID], 
    [CONSTITUENTID],
    [ID])
select @WPSEARCHHISTORYID, 
    [CONSTITUENTID],
    [ID]
from @TempTbl as [temp]
where not exists (select ID from dbo.[WPSEARCHHISTORYSELECTEDCONSTITUENT] as data where data.ID = [temp].ID)

if @@Error <> 0
    return 4;

return 0;