UFN_MKTSMARTFIELD_GETEXTRACONSTITS_DYNAMICSQL_2

Text of dynamic SQL that returns constituents from other tables, taking SITES parameter into account.

Return

Return Type
nvarchar(1000)

Parameters

Parameter	Parameter Type	Mode	Description
@SITES	xml	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSMARTFIELD_GETEXTRACONSTITS_DYNAMICSQL_2]
(
  @SITES xml
)
returns nvarchar(1000)
with execute as caller
as begin
  declare @SQL as nvarchar(1000);

  set @SQL =
  -- update for any constituents who were removed from a REVENUE record (merge, change constit on payment)

  '  union' + char(13) +   
  '  select [CONSTITUENTID] from dbo.[REVENUEAUDIT] inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [REVENUEAUDIT].[CONSTITUENTID] where [REVENUEAUDIT].[AUDITDATE] > @ASOF' + char(13);

  if @SITES is not null
    set @SQL = @SQL + 'and (@SITES is null or exists (select top 1 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [CONSTITUENT].[ID]))' + char(13);

  -- update for any recently added constituents (the result will be 0)

  set @SQL = @SQL +
  '  union' + char(13) +   
  '  select [ID] from dbo.[CONSTITUENT] where [DATEADDED] > @ASOF' + char(13);

  if @SITES is not null
    set @SQL = @SQL + 'and (@SITES is null or exists (select top 1 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [CONSTITUENT].[ID]))' + char(13);

  return @SQL;
end