USP_DATALIST_CMSDISTINCTEDUCATIONALSUBDEPARTMENTS

create procedure dbo.USP_DATALIST_CMSDISTINCTEDUCATIONALSUBDEPARTMENTS
(
    @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 ACADEMICCATALOGSUBDEPARTMENT

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

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

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