USP_DATALIST_PROGRAMREGISTRATIONINFORMATION

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

    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 semicolon 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.PROGRAMID = @PROGRAMID
            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;