UFN_EDUCATIONALHISTORY_ADDITIONALINFORMATIONLIST

CREATE function dbo.UFN_EDUCATIONALHISTORY_ADDITIONALINFORMATIONLIST
(
    @EDUCATIONALHISTORYID uniqueidentifier
)
returns nvarchar(4000)
as
begin
    declare @ACADEMICCATALOGCOLLEGE nvarchar(150);
    declare @ACADEMICCATALOGDIVISION nvarchar(150);
    declare @ACADEMICCATALOGDEPARTMENT nvarchar(150);
    declare @ACADEMICCATALOGSUBDEPARTMENT nvarchar(150);
    declare @ACADEMICCATALOGDEGREETYPE nvarchar(150);
    declare @EDUCATIONALCOLLEGE nvarchar(150);
    declare @EDUCATIONALDIVISION nvarchar(150);
    declare @EDUCATIONALDEPARTMENT nvarchar(150);
    declare @EDUCATIONALSUBDEPARTMENT nvarchar(150);
    declare @EDUCATIONALDEGREETYPE nvarchar(150);
    declare @LIST nvarchar(4000);
    declare @SEPERATOR nvarchar(2);

    declare @USEACADEMICCATALOG bit;                        
    select top 1
        @USEACADEMICCATALOG = EC.USEACADEMICCATALOG
    from
        dbo.EDUCATIONALCONFIGURATION EC;

    set @USEACADEMICCATALOG = coalesce(@USEACADEMICCATALOG,0);

    declare @ISAFFILIATED bit;
    select
        @ISAFFILIATED = INSTITUTION.ISAFFILIATED
    from
        dbo.EDUCATIONALINSTITUTION INSTITUTION
    inner join dbo.EDUCATIONALHISTORY EDUCATION on INSTITUTION.ID = EDUCATION.EDUCATIONALINSTITUTIONID
    where
        EDUCATION.ID = @EDUCATIONALHISTORYID;

    set @LIST = N'';

    declare EDUCATIONCURSOR cursor local fast_forward for
        select
            dbo.UFN_ACADEMICCATALOGCOLLEGE_GETNAME(ACADEMICCATALOGCOLLEGEID) as ACADEMICCATALOGCOLLEGE,
            dbo.UFN_ACADEMICCATALOGDIVISION_GETNAME(ACADEMICCATALOGDIVISIONID) as ACADEMICCATALOGDIVISION,
            dbo.UFN_ACADEMICCATALOGDEPARTMENT_GETNAME(ACADEMICCATALOGDEPARTMENTID) as ACADEMICCATALOGDEPARTMENT,
            dbo.UFN_ACADEMICCATALOGSUBDEPARTMENT_GETNAME(ACADEMICCATALOGSUBDEPARTMENTID) as ACADEMICCATALOGSUBDEPARTMENT,
            dbo.UFN_ACADEMICCATALOGDEGREETYPE_GETNAME(ACADEMICCATALOGDEGREETYPEID) as ACADEMICCATALOGDEGREETYPE,
            dbo.UFN_EDUCATIONALCOLLEGECODE_GETDESCRIPTION(EDUCATIONALCOLLEGECODEID) as EDUCATIONALCOLLEGE,
            dbo.UFN_EDUCATIONALDIVISIONCODE_GETDESCRIPTION(EDUCATIONALDIVISIONCODEID) as EDUCATIONALDIVISION,
            dbo.UFN_EDUCATIONALDEPARTMENTCODE_GETDESCRIPTION(EDUCATIONALDEPARTMENTCODEID) as EDUCATIONALDEPARTMENT,
            dbo.UFN_EDUCATIONALSUBDEPARTMENTCODE_GETDESCRIPTION(EDUCATIONALSUBDEPARTMENTCODEID) as EDUCATIONALSUBDEPARTMENT,
            dbo.UFN_EDUCATIONALDEGREETYPECODE_GETDESCRIPTION(EDUCATIONALDEGREETYPECODEID) as EDUCATIONALDEGREETYPE
        from
            dbo.EDUCATIONADDITIONALINFORMATION
        where
            EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID;

    open EDUCATIONCURSOR;
        fetch next from EDUCATIONCURSOR into @ACADEMICCATALOGCOLLEGE, @ACADEMICCATALOGDIVISION, @ACADEMICCATALOGDEPARTMENT, @ACADEMICCATALOGSUBDEPARTMENT, @ACADEMICCATALOGDEGREETYPE, @EDUCATIONALCOLLEGE, @EDUCATIONALDIVISION, @EDUCATIONALDEPARTMENT, @EDUCATIONALSUBDEPARTMENT, @EDUCATIONALDEGREETYPE;
        set @LIST = '';
        set @SEPERATOR = '; ';

        while @@FETCH_STATUS = 0
        begin
            if @ISAFFILIATED = 1 and @USEACADEMICCATALOG = 1
                begin
                    set @LIST = @LIST + case when @ACADEMICCATALOGCOLLEGE is not null then @ACADEMICCATALOGCOLLEGE else '' end
                    set @LIST = @LIST + case when @ACADEMICCATALOGDIVISION is not null then @SEPERATOR + @ACADEMICCATALOGDIVISION else '' end
                    set @LIST = @LIST + case when @ACADEMICCATALOGDEPARTMENT is not null then @SEPERATOR + @ACADEMICCATALOGDEPARTMENT else '' end
                    set @LIST = @LIST + case when @ACADEMICCATALOGSUBDEPARTMENT is not null then @SEPERATOR + @ACADEMICCATALOGSUBDEPARTMENT else '' end
                    set @LIST = @LIST + case when @ACADEMICCATALOGDEGREETYPE is not null then @SEPERATOR + @ACADEMICCATALOGDEGREETYPE else '' end
                    set @LIST = @LIST + CHAR(10);
                end
            else
                begin
                    set @LIST = @LIST + case when len(@EDUCATIONALCOLLEGE) > 0 then @EDUCATIONALCOLLEGE else '' end
                    set @LIST = @LIST + case when (len(@LIST) > 0 and len(@EDUCATIONALDIVISION) > 0) then @SEPERATOR + @EDUCATIONALDIVISION 
                                        when len(@EDUCATIONALDIVISION) > 0 then @EDUCATIONALDIVISION else '' end
                    set @LIST = @LIST + case when (len(@LIST) > 0 and len(@EDUCATIONALDEPARTMENT) > 0) then @SEPERATOR + @EDUCATIONALDEPARTMENT 
                                        when len(@EDUCATIONALDEPARTMENT) > 0 then @EDUCATIONALDEPARTMENT else '' end
                    set @LIST = @LIST + case when (len(@LIST) > 0 and len(@EDUCATIONALSUBDEPARTMENT) > 0) then @SEPERATOR + @EDUCATIONALSUBDEPARTMENT 
                                        when len(@EDUCATIONALSUBDEPARTMENT) > 0 then @EDUCATIONALSUBDEPARTMENT else '' end
                    set @LIST = @LIST + case when (len(@LIST) > 0 and len(@EDUCATIONALDEGREETYPE) > 0) then @SEPERATOR + @EDUCATIONALDEGREETYPE 
                                        when len(@EDUCATIONALDEGREETYPE) > 0 then @EDUCATIONALDEGREETYPE else '' end
                    set @LIST = @LIST + CHAR(10);
                end
            fetch next from EDUCATIONCURSOR into @ACADEMICCATALOGCOLLEGE, @ACADEMICCATALOGDIVISION, @ACADEMICCATALOGDEPARTMENT, @ACADEMICCATALOGSUBDEPARTMENT, @ACADEMICCATALOGDEGREETYPE, @EDUCATIONALCOLLEGE, @EDUCATIONALDIVISION, @EDUCATIONALDEPARTMENT, @EDUCATIONALSUBDEPARTMENT, @EDUCATIONALDEGREETYPE;
        end

    --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

    close EDUCATIONCURSOR;
    deallocate EDUCATIONCURSOR;

    return @LIST;
end