USP_DATALIST_EVENTREGISTRATIONINFORMATIONSECTION

CREATE procedure dbo.USP_DATALIST_EVENTREGISTRATIONINFORMATIONSECTION
(
    @EVENTID uniqueidentifier = null,
    @PROGRAMID uniqueidentifier = null
)
as begin
    set nocount on;

    declare @TABLENAME nvarchar(100) = 'REGISTRATIONSECTIONCODE';

    declare @HASCUSTOMREGISTRATIONINFORMATION bit;

    declare @REGINFOSECTIONS table (
        REGISTRATIONSECTIONCODEID uniqueidentifier,
        SECTION nvarchar(100),
        SEQUENCE integer
    );

    if @EVENTID is not null
        begin
            select
                @PROGRAMID = EVENT.PROGRAMID,
                @HASCUSTOMREGISTRATIONINFORMATION = EVENT.HASCUSTOMREGISTRATIONINFORMATION
            from dbo.EVENT
            inner join dbo.PROGRAM
                on EVENT.PROGRAMID = PROGRAM.ID
            where EVENT.ID = @EVENTID;

            if @HASCUSTOMREGISTRATIONINFORMATION = 1 begin
                insert into @REGINFOSECTIONS (REGISTRATIONSECTIONCODEID, SECTION, SEQUENCE)
                    select 
                        REGISTRATIONSECTIONCODE.ID as REGISTRATIONSECTIONCODEID,
                        REGISTRATIONSECTIONCODE.DESCRIPTION as SECTION,
                        REGISTRATIONSECTIONCODE.SEQUENCE
                    from dbo.PROGRAMEVENTREGISTRATIONSECTION
                    inner join dbo.REGISTRATIONSECTIONCODE
                        on PROGRAMEVENTREGISTRATIONSECTION.REGISTRATIONSECTIONCODEID = REGISTRATIONSECTIONCODE.ID
                    where PROGRAMEVENTREGISTRATIONSECTION.EVENTID = @EVENTID;

            end else begin
                insert into @REGINFOSECTIONS (REGISTRATIONSECTIONCODEID, SECTION, SEQUENCE)
                    select 
                        REGISTRATIONSECTIONCODE.ID as REGISTRATIONSECTIONCODEID,
                        REGISTRATIONSECTIONCODE.DESCRIPTION as SECTION,
                        REGISTRATIONSECTIONCODE.SEQUENCE
                    from dbo.PROGRAMEVENTREGISTRATIONSECTION
                    inner join dbo.REGISTRATIONSECTIONCODE
                        on PROGRAMEVENTREGISTRATIONSECTION.REGISTRATIONSECTIONCODEID = REGISTRATIONSECTIONCODE.ID
                    where PROGRAMEVENTREGISTRATIONSECTION.PROGRAMID = @PROGRAMID;
            end
        end

    else if @PROGRAMID is not null
        insert into @REGINFOSECTIONS (REGISTRATIONSECTIONCODEID, SECTION, SEQUENCE)
            select 
                REGISTRATIONSECTIONCODE.ID as REGISTRATIONSECTIONCODEID,
                REGISTRATIONSECTIONCODE.DESCRIPTION as SECTION,
                REGISTRATIONSECTIONCODE.SEQUENCE
            from dbo.PROGRAMEVENTREGISTRATIONSECTION
            inner join dbo.REGISTRATIONSECTIONCODE
                on PROGRAMEVENTREGISTRATIONSECTION.REGISTRATIONSECTIONCODEID = REGISTRATIONSECTIONCODE.ID
            where PROGRAMEVENTREGISTRATIONSECTION.PROGRAMID = @PROGRAMID;

    else
        insert into @REGINFOSECTIONS (REGISTRATIONSECTIONCODEID, SECTION, SEQUENCE)
            select 
                REGISTRATIONSECTIONCODE.ID as REGISTRATIONSECTIONCODEID,
                REGISTRATIONSECTIONCODE.DESCRIPTION as SECTION,
                REGISTRATIONSECTIONCODE.SEQUENCE
            from dbo.REGISTRATIONSECTIONCODE
            order by SEQUENCE;

    if dbo.UFN_GETLISTSORTMETHOD(@TABLENAME) = 0
        select 
            REGISTRATIONSECTIONCODEID,
            SECTION
        from @REGINFOSECTIONS
        order by SECTION;
    else
        select 
            REGISTRATIONSECTIONCODEID,
            SECTION
        from @REGINFOSECTIONS
        order by SEQUENCE;

end