USP_DATALIST_EVENTREGISTRATIONINFORMATION

CREATE procedure dbo.USP_DATALIST_EVENTREGISTRATIONINFORMATION (
    @EVENTID uniqueidentifier,
    @REGISTRATIONINFORMATIONTYPECODE tinyint = null
) as
    set nocount on;

    declare @PROGRAMID uniqueidentifier
    declare @HASCUSTOMREGISTRATIONINFORMATION bit = 0
    select @PROGRAMID = PROGRAMID, @HASCUSTOMREGISTRATIONINFORMATION = HASCUSTOMREGISTRATIONINFORMATION from dbo.EVENT where ID = @EVENTID

    if @HASCUSTOMREGISTRATIONINFORMATION = 0 begin
        execute dbo.USP_DATALIST_PROGRAMREGISTRATIONINFORMATION @PROGRAMID, @REGISTRATIONINFORMATIONTYPECODE
    end else begin
        ;with REGINFO_CTE as (
            select
                persri.ID,
                REGISTRATIONSECTIONCODE.DESCRIPTION as REGISTRATIONSECTIONCODEDESCRIPTION,
                ri.NAME,
                case ri.TYPECODE --0 = Announcement, 1 = Question

                    when 0 then null
                    when 1 then ONLINEHTML
                end as ONLINEHTML,
                case ri.TYPECODE
                    when 0 then 'Notice/disclaimer'
                    when 1 then ri.RESPONSETYPE
                end as TYPE,
                case ri.TYPECODE
                    when 0 then null 
                    when 1 then 
                        case ri.RESPONSETYPECODE
                            when 3 then null
                            else persri.[REQUIRED]
                        end
                end as [REQUIRED],
                case ri.RESPONSETYPECODE 
                    when 2 then --delimited list of the options

                        stuff(
                            (select '; ' + rio.NAME  
                            from dbo.REGISTRATIONINFORMATIONOPTION as rio
                            where rio.REGISTRATIONINFORMATIONID = persri.REGISTRATIONINFORMATIONID 
                            order by rio.SEQUENCE
                            for xml path(''), TYPE).value('.', 'nvarchar(max)')
                        , 1, 2, '')--replace the leading comma with an empty string  

                end as [OPTIONS],
                dense_rank() over (order by pers.SEQUENCE, REGISTRATIONSECTIONCODE.DESCRIPTION) as RANKNUMBER,
                pers.SEQUENCE as SECTIONSEQUENCE,
                persri.SEQUENCE as REGINFOSEQUENCE
            from dbo.PROGRAMEVENTREGISTRATIONSECTION as pers
            inner join dbo.PROGRAMEVENTREGISTRATIONSECTIONREGISTRATIONINFORMATION as persri
                on pers.ID = persri.PROGRAMEVENTREGISTRATIONSECTIONID 
            inner join dbo.REGISTRATIONINFORMATION as ri
                on persri.REGISTRATIONINFORMATIONID = ri.ID 
            inner join dbo.REGISTRATIONSECTIONCODE
                on REGISTRATIONSECTIONCODE.ID = pers.REGISTRATIONSECTIONCODEID
            where
                pers.EVENTID = @EVENTID
                and (@REGISTRATIONINFORMATIONTYPECODE is null or ri.TYPECODE = @REGISTRATIONINFORMATIONTYPECODE)
        )
        select
            REGINFO_CTE.ID,
            PREFIXSECTION.PREFIXDESCRIPTION as REGISTRATIONSECTIONCODEDESCRIPTION,
            REGINFO_CTE.NAME,
            REGINFO_CTE.ONLINEHTML,
            REGINFO_CTE.TYPE,
            REGINFO_CTE.REQUIRED,
            REGINFO_CTE.OPTIONS
        from
            REGINFO_CTE
        outer apply
            dbo.UFN_PROGRAMEVENTREGISTRATIONSECTION_PREFIXSECTIONINLINE(REGINFO_CTE.RANKNUMBER, REGISTRATIONSECTIONCODEDESCRIPTION) as PREFIXSECTION
        order by
            REGINFO_CTE.SECTIONSEQUENCE, REGINFO_CTE.REGINFOSEQUENCE;
    end

    return 0;