USP_DATALIST_CMSDISTINCTEDUCATIONALPROGRAMS

create procedure dbo.USP_DATALIST_CMSDISTINCTEDUCATIONALPROGRAMS
(
    @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 ACADEMICCATALOGPROGRAM

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

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

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