USP_DATALIST_REGISTRATIONINFORMATION

CREATE procedure dbo.USP_DATALIST_REGISTRATIONINFORMATION
(
    @TYPECODE tinyint = null,
    @INCLUDEINACTIVE bit = 0
)
as
    set nocount on;

    select 
        ID,
        NAME,
        case
            when TYPECODE = 1 then ONLINEHTML
            else ''
        end as WEBTEXT,
        case
            when TYPECODE = 0 then 'Notice/disclaimer'
            else RESPONSETYPE
        end as TYPE,
        case RESPONSETYPECODE
            when 2 then    (            -- Dropdown list

                    stuff(
                        (select '; ' + NAME
                        from dbo.REGISTRATIONINFORMATIONOPTION
                        where REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID
                        order by SEQUENCE
                        for xml path(''), type).value('.', 'nvarchar(max)'),
                        1, 2, ''
                    )
                ) 
            when 4 then    (            -- Name/phone/email                

                    case ASKPERSONPHONE2
                        when 1 then 'Include 2nd phone'
                        else ''
                    end
                    +
                    case
                        when ASKPERSONPHONE2 = 1 and ASKPERSONEMAIL = 1 then ', '
                        else ''
                    end
                    +
                    case ASKPERSONEMAIL
                        when 1 then 'Include email'
                        else ''
                    end
                )
            else ''
        end as OPTIONS,        
        case
            when ALLPRICETYPES = 1 then 'All'
            else (
                stuff(
                    (select '; ' + PRICETYPECODE.DESCRIPTION
                    from dbo.REGISTRATIONINFORMATIONPRICETYPE
                    inner join dbo.PRICETYPECODE
                        on REGISTRATIONINFORMATIONPRICETYPE.PRICETYPECODEID = PRICETYPECODE.ID
                    where REGISTRATIONINFORMATIONPRICETYPE.REGISTRATIONINFORMATIONID = REGISTRATIONINFORMATION.ID
                    order by PRICETYPECODE.DESCRIPTION
                    for xml path(''), type).value('.', 'nvarchar(max)'),
                    1, 2, ''
                )
            )                
        end as PRICETYPES,
        DISPLAYONLINE,
        case INACTIVE
            when 0 then 1
            else 0
        end as ACTIVE
    from dbo.REGISTRATIONINFORMATION
    where (
            @TYPECODE is null 
            or (@TYPECODE = 0 and TYPECODE = 0)
            or (@TYPECODE > 0 and TYPECODE = 1 and RESPONSETYPECODE = (@TYPECODE - 1))
        )
        and (@INCLUDEINACTIVE = 1 or INACTIVE = 0);