USP_DATALIST_CMSDISTINCTEDUCATIONALDEPARTMENTS

create procedure dbo.USP_DATALIST_CMSDISTINCTEDUCATIONALDEPARTMENTS
(
    @USEACADEMICCATALOG bit = 0,
  @AFFILIATEDONLY bit = 0
)
as
    set nocount on;

-- If Academic Catalog is turned on AND we only want the affiliated institution information, get strictly from ACADEMICCATALOGDEPARTMENT

  if @USEACADEMICCATALOG = 1 and @AFFILIATEDONLY = 1
  begin
    select distinct AC.NAME
    from
       dbo.ACADEMICCATALOGDEPARTMENT AC;
  end
-- If Academic Catalog is turned on AND we want both affiliated and non-affiliated institution information, grab from ACADEMICCATALOGDEPARTMENT and EDUCATIONALDEPARTMENTCODE

  else if @USEACADEMICCATALOG = 1 and @AFFILIATEDONLY = 0
  begin
    select distinct EC.DESCRIPTION as NAME
    from
         dbo.EDUCATIONALDEPARTMENTCODE EC
    union 
    select distinct AC.NAME
    from
         dbo.ACADEMICCATALOGDEPARTMENT AC;
  end
-- If Academic Catalog is turned off then we grab only from EDUCATIONALDEPARTMENTCODE

  else
  begin
    select distinct EC.DESCRIPTION as NAME
    from
      dbo.EDUCATIONALDEPARTMENTCODE EC;
  end