USP_DATAFORMTEMPLATE_VIEW_PREREGISTEREDPROGRAMEVENTREGISTRANTREGISTRATIONINFORMATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PREREGISTEREDPROGRAMEVENTREGISTRANTREGISTRATIONINFORMATION
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @REGISTRANTREGISTRATIONINFORMATION xml = null output
)
as begin
    set nocount on;

    set @DATALOADED = 1;

    set @REGISTRANTREGISTRATIONINFORMATION = (
            select                 
                REGISTRANTREGISTRATIONINFORMATION.RESPONSETYPECODE,
                REGISTRANTREGISTRATIONINFORMATION.PERSONDETAILTYPECODE,
                REGISTRANTREGISTRATIONINFORMATION.NAME as QUESTION,
                case 
                    when RESPONSETYPECODE in (0, 1, 4) then TEXTVALUE
                    when RESPONSETYPECODE = 2 then REGISTRATIONINFORMATIONOPTION.NAME
                    when RESPONSETYPECODE = 3 then                     
                        case isnull(REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONOPTIONID, '00000000-0000-0000-0000-000000000000')
                            when '00000000-0000-0000-0000-000000000000' then '0'
                            when '11111111-1111-1111-1111-111111111111' then '1'
                        end
                    else ''
                end as RESPONSE
            from dbo.UFN_REGISTRANT_GETREGISTRATIONINFORMATION(@ID) REGISTRANTREGISTRATIONINFORMATION
            left outer join dbo.REGISTRATIONINFORMATIONOPTION
                on REGISTRANTREGISTRATIONINFORMATION.REGISTRATIONINFORMATIONOPTIONID = REGISTRATIONINFORMATIONOPTION.ID    
            order by REGISTRANTREGISTRATIONINFORMATION.SECTIONSEQUENCE, REGISTRANTREGISTRATIONINFORMATION.SEQUENCE, REGISTRANTREGISTRATIONINFORMATION.PERSONDETAILTYPECODE
            for xml raw('ITEM'),type,elements,root('REGISTRANTREGISTRATIONINFORMATION'),binary base64
        );

    return 0;
end