USP_DATALIST_EDUCATIONALDEPARTMENTCODEEXTENSION

CREATE procedure dbo.USP_DATALIST_EDUCATIONALDEPARTMENTCODEEXTENSION
(
  @INCLUDEINACTIVE bit = 1
)
with execute as owner
AS
  set nocount on;

  declare @TABLENAME nvarchar(128);
  set @TABLENAME = 'EDUCATIONALDEPARTMENTCODE';

  if @INCLUDEINACTIVE is null set @INCLUDEINACTIVE=0;

  declare @sql nvarchar(500);

  --sites table doesn't get created until used

  declare @SITETABLENAME nvarchar(128) = @TABLENAME + 'SITE'
  if isnull(object_id(@SITETABLENAME),0) > 0
    set @sql='SELECT ID, DESCRIPTION, ACTIVE, SEQUENCE, (select dbo.UDA_BUILDLIST(SITE.NAME) from dbo.' + @SITETABLENAME + ' inner join dbo.site on ' + @SITETABLENAME + '.SITEID = SITE.ID where ' + @SITETABLENAME + '.' + @TABLENAME + 'ID = ' + @TABLENAME + '.ID) from  dbo.[' + @TABLENAME + ']';
  else
    set @sql='SELECT ID, DESCRIPTION, ACTIVE, SEQUENCE, ''All sites'' from  dbo.[' + @TABLENAME + ']';          

  declare @t table (ID uniqueidentifier,DESCRIPTION nvarchar(100),ACTIVE bit,SEQUENCE int, SITES nvarchar(max));

  insert into @t exec (@sql);

  if dbo.UFN_GETLISTSORTMETHOD(@TABLENAME) = 0
    select T.ID, T.DESCRIPTION, EXT.SHORTCODE, T.ACTIVE, case when SITES = '' then 'All sites' else SITES end as SITES from @t T left join dbo.EDUCATIONALDEPARTMENTCODEEXTENSION EXT on T.ID = EXT.ID where T.ACTIVE = 1 OR @INCLUDEINACTIVE=1 order by T.DESCRIPTION;
  else
    select T.ID, T.DESCRIPTION, EXT.SHORTCODE, T.ACTIVE, case when SITES = '' then 'All sites' else SITES end as SITES from @t T left join dbo.EDUCATIONALDEPARTMENTCODEEXTENSION EXT on T.ID = EXT.ID where T.ACTIVE = 1 OR @INCLUDEINACTIVE=1 order by T.SEQUENCE;