USP_VOLUNTEERSKILLLEVEL_UPDATEFROMXML

            create procedure dbo.USP_VOLUNTEERSKILLLEVEL_UPDATEFROMXML(
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
                set nocount on;

                if @CHANGEAGENTID is null
                    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

                declare @TempTbl table (
                   [ID] uniqueidentifier,
                   [DESCRIPTION] nvarchar(25),
                   [SEQUENCE] int)

                insert into @TempTbl select 
                    [ID],
                    [DESCRIPTION],
                    [SEQUENCE]
                from dbo.UFN_VOLUNTEERSKILLLEVEL_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 table

                delete from dbo.[VOLUNTEERSKILLLEVEL] where 
                [VOLUNTEERSKILLLEVEL].ID not in (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 [VOLUNTEERSKILLLEVEL]
                        set 
                            [VOLUNTEERSKILLLEVEL].[ID]=temp.[ID],
                            [VOLUNTEERSKILLLEVEL].[DESCRIPTION]=temp.[DESCRIPTION],
                            [VOLUNTEERSKILLLEVEL].[SEQUENCE]=temp.[SEQUENCE],
                            [VOLUNTEERSKILLLEVEL].CHANGEDBYID = @CHANGEAGENTID,
                            [VOLUNTEERSKILLLEVEL].DATECHANGED = getdate()

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

                if @@Error <> 0
                    return 3;    

                -- insert new items

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

                if @@Error <> 0
                    return 4;

                return 0;