UFN_ACADEMICCATALOG_GETPARENT

CREATE function dbo.UFN_ACADEMICCATALOG_GETPARENT
(
    @LEVELCODE tinyint,
    @ID uniqueidentifier
)
returns nvarchar(150) with execute as caller
as
begin
    if @ID is null or @LEVELCODE not in (1,2,3,4,5,6,7)
        return '';

    declare @PARENT nvarchar(150);

    if @LEVELCODE = 1
        select
            @PARENT = INSTITUTION.NAME
        from
            dbo.ACADEMICCATALOGPROGRAM as PROGRAM
        inner join dbo.EDUCATIONALINSTITUTION as INSTITUTION on PROGRAM.EDUCATIONALINSTITUTIONID = INSTITUTION.ID
        where
            PROGRAM.ID = @ID;

    if @LEVELCODE = 2
        select
            @PARENT = PROGRAM.PROGRAM
        from
            dbo.ACADEMICCATALOGDEGREE as DEGREE
        inner join dbo.ACADEMICCATALOGPROGRAM as PROGRAM on DEGREE.ACADEMICCATALOGPROGRAMID = PROGRAM.ID
        where
            DEGREE.ID = @ID;

    if @LEVELCODE = 3
        select
            @PARENT = DEGREE.NAME
        from
            dbo.ACADEMICCATALOGCOLLEGE as COLLEGE
        inner join dbo.ACADEMICCATALOGDEGREE as DEGREE on COLLEGE.ACADEMICCATALOGDEGREEID = DEGREE.ID
        where
            COLLEGE.ID = @ID;

    if @LEVELCODE = 4
        select
            @PARENT = COLLEGE.NAME
        from
            dbo.ACADEMICCATALOGDIVISION as DIVISION
        inner join dbo.ACADEMICCATALOGCOLLEGE as COLLEGE on DIVISION.ACADEMICCATALOGCOLLEGEID = COLLEGE.ID
        where
            DIVISION.ID = @ID;

    if @LEVELCODE = 5 
        select
            @PARENT = coalesce(COLLEGE.NAME, DIVISION.NAME)
        from
            dbo.ACADEMICCATALOGDEPARTMENT as DEPARTMENT
        left join dbo.ACADEMICCATALOGCOLLEGE as COLLEGE on DEPARTMENT.ACADEMICCATALOGCOLLEGEID = COLLEGE.ID
        left join dbo.ACADEMICCATALOGDIVISION as DIVISION on DEPARTMENT.ACADEMICCATALOGDIVISIONID = DIVISION.ID
        where
            DEPARTMENT.ID = @ID;

    if @LEVELCODE = 6
        select
            @PARENT = DEPARTMENT.NAME
        from
            dbo.ACADEMICCATALOGSUBDEPARTMENT as SUBDEPARTMENT
        inner join dbo.ACADEMICCATALOGDEPARTMENT as DEPARTMENT on SUBDEPARTMENT.ACADEMICCATALOGDEPARTMENTID = DEPARTMENT.ID
        where
            SUBDEPARTMENT.ID = @ID;

    if @LEVELCODE = 7 
        select
            @PARENT = coalesce(DEPARTMENT.NAME, SUBDEPARTMENT.NAME)
        from
            dbo.ACADEMICCATALOGDEGREETYPE as DEGREETYPE
        left join dbo.ACADEMICCATALOGDEPARTMENT as DEPARTMENT on DEGREETYPE.ACADEMICCATALOGDEPARTMENTID = DEPARTMENT.ID
        left join dbo.ACADEMICCATALOGSUBDEPARTMENT as SUBDEPARTMENT on DEGREETYPE.ACADEMICCATALOGSUBDEPARTMENTID = SUBDEPARTMENT.ID
        where
            DEGREETYPE.ID = @ID;

    return @PARENT;
end