USP_DATAFORMTEMPLATE_EDITLOAD_PROGRAMREGISTRATIONINFORMATIONREORDER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PROGRAMREGISTRATIONINFORMATIONREORDER
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @PROGRAMEVENTREGISTRATIONSECTIONS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 0;
    set @TSLONG = 0;

    select @DATALOADED = 1
    from dbo.PROGRAM
    where ID = @ID;

    set @PROGRAMEVENTREGISTRATIONSECTIONS = (
        select
            SECTION.ID,
            REGISTRATIONSECTIONCODE.DESCRIPTION,
            SECTION.SEQUENCE,
            (
                select
                    SECTIONINFO.ID,
                    REGISTRATIONINFORMATION.NAME as REGISTRATIONINFORMATIONNAME,
                    SECTIONINFO.SEQUENCE
                from
                    dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION as SECTIONINFO
                inner join
                    dbo.REGISTRATIONINFORMATION on REGISTRATIONINFORMATION.ID = SECTIONINFO.REGISTRATIONINFORMATIONID
                where
                    SECTIONINFO.PROGRAMEVENTREGISTRATIONSECTIONID = SECTION.ID
                order by
                    SECTIONINFO.SEQUENCE
                for xml raw('ITEM'), type, root('PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION'), binary base64
            )
        from
            dbo.PROGRAMEVENTREGISTRATIONSECTION as SECTION
        inner join
            dbo.REGISTRATIONSECTIONCODE on REGISTRATIONSECTIONCODE.ID = SECTION.REGISTRATIONSECTIONCODEID
        where
            SECTION.PROGRAMID = @ID
        order by
            SECTION.SEQUENCE
        for xml raw('ITEM'), type, root('PROGRAMEVENTREGISTRATIONSECTIONS'), binary base64
    );

    select @TSLONG = max(TSLONG)
    from (
        select TSLONG
        from dbo.PROGRAMEVENTREGISTRATIONSECTION
        where PROGRAMID = @ID

        union all
        select SECTIONINFO.TSLONG
        from dbo.PROGRAMEVENTREGISTRATIONSECTION as SECTION
        inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION as SECTIONINFO on SECTIONINFO.PROGRAMEVENTREGISTRATIONSECTIONID = SECTION.ID
        where SECTION.PROGRAMID = @ID
    ) as TSLONGS

    return 0;