USP_LIKELIHOODPERCENTS_CUSTOMUPDATEFROMXML

            CREATE procedure [dbo].[USP_LIKELIHOODPERCENTS_CUSTOMUPDATEFROMXML] 
            (
                @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,
                   [DESCRIPTION] nvarchar(100),
                   [PERCENT] tinyint,
                   [SEQUENCE] int )

                insert into @TempTbl select 
                    [ID],
                    [DESCRIPTION],
                    [PERCENT],
                    [SEQUENCE]
                from dbo.UFN_LIKELIHOODPERCENTS_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.[LIKELIHOODTYPECODE] 
                where 
                    [LIKELIHOODTYPECODE].ID in 
                        (select ID from dbo.UFN_LIKELIHOODPERCENTS()
                        except select ID from @TempTbl [TEMP])    

                delete from 
                    dbo.[LIKELIHOODPERCENT] 
                where 
                    [LIKELIHOODPERCENT].ID in 
                        (select ID from dbo.UFN_LIKELIHOODPERCENTS()
                        except select ID from @TempTbl [TEMP] where [TEMP].[PERCENT] is not null)

                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 [LIKELIHOODTYPECODE]
                    set [LIKELIHOODTYPECODE].[ID] = temp.[ID],
                        [LIKELIHOODTYPECODE].[DESCRIPTION] = temp.[DESCRIPTION],
                        [LIKELIHOODTYPECODE].[SEQUENCE] = temp.[SEQUENCE],
                        [LIKELIHOODTYPECODE].CHANGEDBYID = @CHANGEAGENTID,
                        [LIKELIHOODTYPECODE].DATECHANGED = @CHANGEDATE
                    from 
                        dbo.[LIKELIHOODTYPECODE] 
                        inner join @TempTbl as [TEMP] on [LIKELIHOODTYPECODE].ID = [TEMP].ID
                    where
                        ([LIKELIHOODTYPECODE].[DESCRIPTION] <> [TEMP].[DESCRIPTION]) or
                        ([LIKELIHOODTYPECODE].[SEQUENCE] <> [TEMP].[SEQUENCE])

                update [LIKELIHOODPERCENT]
                    set [LIKELIHOODPERCENT].[ID] = temp.[ID],
                        [LIKELIHOODPERCENT].[PERCENT] = temp.[PERCENT],
                        [LIKELIHOODPERCENT].CHANGEDBYID = @CHANGEAGENTID,
                        [LIKELIHOODPERCENT].DATECHANGED = @CHANGEDATE
                    from 
                        dbo.[LIKELIHOODPERCENT] 
                        inner join @TempTbl as [TEMP] on [LIKELIHOODPERCENT].ID = [TEMP].ID
                    where
                        [TEMP].[PERCENT] is not null and (
                        ([LIKELIHOODPERCENT].[PERCENT] <> [TEMP].[PERCENT]) or 
                        ([LIKELIHOODPERCENT].[PERCENT] is null and [TEMP].[PERCENT] is not null) or 
                        ([LIKELIHOODPERCENT].[PERCENT] is not null and [TEMP].[PERCENT] is null))

                if @@Error <> 0
                    return 3;    

                -- insert new items

                insert into [LIKELIHOODTYPECODE]  
                    ([ID],
                    [DESCRIPTION],
                    [SEQUENCE],
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select 
                    [ID],
                    [DESCRIPTION],
                    [SEQUENCE],
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from 
                    @TempTbl as [TEMP]
                where 
                    not exists (select ID from dbo.[LIKELIHOODTYPECODE] as data where data.ID = [TEMP].ID)

                insert into [LIKELIHOODPERCENT]  
                    ([ID],
                    [PERCENT],        
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select 
                    [ID],
                    [PERCENT],
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from 
                    @TempTbl as [TEMP]
                where 
                    [TEMP].[PERCENT] is not null and
                    not exists (select ID from dbo.[LIKELIHOODPERCENT] as data where data.ID = [TEMP].ID)

                if @@Error <> 0
                    return 4;

                return 0;