USP_DATALIST_MAILINGSCHEDULEREPORT

CREATE procedure dbo.[USP_DATALIST_MAILINGSCHEDULEREPORT]
(
  @FROMDATE datetime = null,
  @TODATE datetime = null,
  @SOURCECODEID uniqueidentifier = null,
  @IDSETREGISTERID uniqueidentifier = null,
  @INCLUDEWITHNODATE bit = 0
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);

  declare @DBOBJECTNAME nvarchar(128);
  declare @DBOBJECTTYPE smallint;

  set @SQL = 
  '
    select
      [MKTSEGMENTATION].[ID] as [MAILINGID],
      [MKTSEGMENTATION].[NAME],
      [MKTSEGMENTATION].[CODE],
      [MKTSEGMENTATION].[MAILDATE],
      [MKTSEGMENTATION].[ACTIVATEDATE],
      case [MKTSEGMENTATION].[ACTIVE] when 1 then ''Yes'' else ''No'' end as [ACTIVE],
      [MKTSEGMENTATION].[DESCRIPTION],
      isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE]) as [SCHEDULEDATE]
    from dbo.[MKTSEGMENTATION]
  ';

  if @IDSETREGISTERID is not null
  begin
    select @DBOBJECTNAME = DBOBJECTNAME, 
           @DBOBJECTTYPE = OBJECTTYPE 
    from dbo.[IDSETREGISTER] 
    where [IDSETREGISTER].[ID] = @IDSETREGISTERID;

    if @DBOBJECTTYPE = 1 
      set @DBOBJECTNAME = @DBOBJECTNAME + '()';
    else if @DBOBJECTTYPE = 2 
      set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @IDSETREGISTERID) + ''')';

    set @SQL = @SQL + 'inner join ' + @DBOBJECTNAME + ' as SELECTION on SELECTION.[ID] = [MKTSEGMENTATION].[ID]';
  end

  set @SQL = @SQL + 
  '
    where (@FROMDATE is null or isnull([MAILDATE], [ACTIVATEDATE]) >= @FROMDATE)
      and (@TODATE is null or isnull([MAILDATE], [ACTIVATEDATE]) < dateadd(day, 1, @TODATE))
      and (@SOURCECODEID is null or [MKTSEGMENTATION].[SOURCECODEID] = @SOURCECODEID)
  ';

  set @SQL = @SQL +
    case
      when @INCLUDEWITHNODATE = 1 then 
      '  
        or (ISNULL([MAILDATE], [ACTIVATEDATE]) is null)
       order by [MAILDATE], [NAME];
      '
      else 
      '
        order by [MAILDATE], [NAME]; 
      '
    end;

  exec sp_executesql @SQL, 
    N'@FROMDATE datetime, @TODATE datetime, @SOURCECODEID uniqueidentifier, @INCLUDEWITHNODATE bit',
    @FROMDATE=@FROMDATE, @TODATE=@TODATE, @SOURCECODEID=@SOURCECODEID, @INCLUDEWITHNODATE=@INCLUDEWITHNODATE;


  return 0;