USP_DATAFORMTEMPLATE_EDIT_CONSTITUENCYDISPLAYORDER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENCYDISPLAYORDER (
    @CHANGEAGENTID uniqueidentifier = null,
    @CONSTITUENCIES xml
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        -- handle updating the data

        merge into 
            dbo.CONSTITUENCYDISPLAYORDER
        using (
            select 
                c.value('ID[1]', 'uniqueidentifier') as ID, 
                c.value('SEQUENCE[1]', 'int') as SEQUENCE 
            from 
                @CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') as T(c)) as source
        on 
            CONSTITUENCYDISPLAYORDER.CONSTITUENCYCODEID = source.ID
        when not matched by target then
            insert (ID, CONSTITUENCYCODEID, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values (newid(), ID, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
        when matched and CONSTITUENCYDISPLAYORDER.SEQUENCE <> source.SEQUENCE then
            update set SEQUENCE = source.SEQUENCE, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
        when not matched by source then 
            delete;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;