USP_PROGRAMEVENTREGISTRATIONINFORMATION_REORDERUPDATE

create procedure dbo.USP_PROGRAMEVENTREGISTRATIONINFORMATION_REORDERUPDATE (
    @PROGRAMEVENTREGISTRATIONSECTIONS xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
) as
    set nocount on;

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

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    begin try
        update dbo.PROGRAMEVENTREGISTRATIONSECTION set
            SEQUENCE = c.value('@SEQUENCE[1]', 'int'),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            @PROGRAMEVENTREGISTRATIONSECTIONS.nodes('/PROGRAMEVENTREGISTRATIONSECTIONS/ITEM') as T(c)
        where
            c.value('@ID[1]', 'uniqueidentifier') = PROGRAMEVENTREGISTRATIONSECTION.ID
            and c.value('@SEQUENCE[1]', 'int') <> PROGRAMEVENTREGISTRATIONSECTION.SEQUENCE;

        update dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION set
            SEQUENCE = c.value('@SEQUENCE[1]', 'int'),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from
            @PROGRAMEVENTREGISTRATIONSECTIONS.nodes('/PROGRAMEVENTREGISTRATIONSECTIONS/ITEM/PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION/ITEM') as T(c)
        where
            c.value('@ID[1]', 'uniqueidentifier') = PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.ID
            and c.value('@SEQUENCE[1]', 'int') <> PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION.SEQUENCE;
    end try

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

    return 0;