UFN_VOLUNTEER_TRAITS

            create function dbo.UFN_VOLUNTEER_TRAITS(@VOLUNTEERID uniqueidentifier)
            returns @TRAITS table 
                (
                    ID uniqueidentifier, 
                    TRAITTYPEID tinyint,
                    TRAITTYPE nvarchar(25),
                    SKILL nvarchar(100),
                    SKILLLEVEL nvarchar(100),
                    SKILLLEVELSEQUENCE int,
                    INTEREST nvarchar(100),
                    SPECIALNEED nvarchar(100),
                    CERTIFICATION nvarchar(100),
                    CERTIFICATE nvarchar(25),
                    COURSE nvarchar(100),
                    LICENSE nvarchar(100),
                    LICENSENUMBER nvarchar(25),
                    MEDICAL nvarchar(100),
                    ADMINISTRATIVE nvarchar(100),
                    EXPIRESON datetime,
                    VERIFIED bit
                ) with execute as caller
            as begin
                --Administrative

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, ADMINISTRATIVE,EXPIRESON,VERIFIED)
                    select    VA.ID, 
                            0,
                            'Administrative',
                            ADMINISTRATIVE.DESCRIPTION,
                            VA.EXPIRESON,
                            VA.VERIFIED
                    from VOLUNTEERADMINISTRATIVE VA
                    inner join VOLUNTEERADMINISTRATIVECODE ADMINISTRATIVE on ADMINISTRATIVE.ID = VA.ADMINISTRATIVECODEID
                    where VA.VOLUNTEERID = @VOLUNTEERID;

                --Certification

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, CERTIFICATION, CERTIFICATE,EXPIRESON,VERIFIED)
                    select    VC.ID, 
                            1,
                            'Certification',
                            CERTIFICATION.DESCRIPTION,
                            VC.CERTIFICATE,
                            VC.EXPIRESON,
                            VERIFIED
                    from VOLUNTEERCERTIFICATION VC
                    inner join VOLUNTEERCERTIFICATIONCODE CERTIFICATION on CERTIFICATION.ID = VC.CERTIFICATIONCODEID
                    where VC.VOLUNTEERID = @VOLUNTEERID;

                --Course

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, COURSE,EXPIRESON,VERIFIED)
                    select    VC.ID, 
                            2,
                            'Course',
                            COURSE.DESCRIPTION,
                            VC.EXPIRESON,
                            VERIFIED
                    from VOLUNTEERCOURSE VC
                    inner join VOLUNTEERCOURSECODE COURSE on COURSE.ID = VC.COURSECODEID
                    where VC.VOLUNTEERID = @VOLUNTEERID;

                --Interest

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, INTEREST)
                    select    VI.ID, 
                            3,
                            'Interest',
                            INTEREST.DESCRIPTION
                    from VOLUNTEERINTEREST VI
                    inner join VOLUNTEERINTERESTCODE INTEREST on INTEREST.ID = VI.VOLUNTEERINTERESTCODEID
                    where VI.VOLUNTEERID = @VOLUNTEERID;

                --License

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, LICENSE, LICENSENUMBER,EXPIRESON,VERIFIED)
                    select    VL.ID, 
                            4,
                            'License',
                            LICENSE.DESCRIPTION,
                            VL.LICENSENUMBER,
                            VL.EXPIRESON,
                            VERIFIED
                    from VOLUNTEERLICENSE VL
                    inner join VOLUNTEERLICENSECODE LICENSE on LICENSE.ID = VL.LICENSECODEID
                    where VL.VOLUNTEERID = @VOLUNTEERID;

                --Medical

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, MEDICAL,EXPIRESON,VERIFIED)
                    select    VM.ID, 
                            5,
                            'Medical',
                            MEDICAL.DESCRIPTION,
                            VM.EXPIRESON,
                            VERIFIED
                    from VOLUNTEERMEDICAL VM
                    inner join VOLUNTEERMEDICALCODE MEDICAL on MEDICAL.ID = VM.MEDICALCODEID
                    where VM.VOLUNTEERID = @VOLUNTEERID;

                --Skills

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, SKILL, SKILLLEVEL, SKILLLEVELSEQUENCE)
                    select    VS.ID,
                            6,
                            'Skill',
                            SKILL.DESCRIPTION,
                            SKILLLEVEL.DESCRIPTION,
                            SKILLLEVEL.SEQUENCE
                    from VOLUNTEERSKILL VS
                    inner join VOLUNTEERSKILLCODE SKILL on SKILL.ID = VS.SKILLCODEID
                    inner join VOLUNTEERSKILLLEVEL SKILLLEVEL on SKILLLEVEL.ID = VS.SKILLLEVELID
                    where VS.VOLUNTEERID = @VOLUNTEERID;


                --Special need

                insert into @TRAITS(ID, TRAITTYPEID, TRAITTYPE, SPECIALNEED)
                    select    VS.ID, 
                            7,
                            'Special Need',
                            SPECIALNEED.DESCRIPTION
                    from VOLUNTEERSPECIALNEED VS
                    inner join VOLUNTEERSPECIALNEEDCODE SPECIALNEED on SPECIALNEED.ID = VS.SPECIALNEEDCODEID
                    where VS.VOLUNTEERID = @VOLUNTEERID;

                return;
            end