USP_CONSTITUENTSMARTFIELD_HASCONTACTINFORMATION

    CREATE procedure dbo.[USP_CONSTITUENTSMARTFIELD_HASCONTACTINFORMATION]
    (
      @CHECKEMAIL bit,
      @CHECKPHONE bit,
      @CHECKADDRESS bit,
      @ASOF datetime,
      @SMARTFIELDID uniqueidentifier = null,
      @SITES xml = null
    )
    as
    begin
        set nocount on;

        if not exists (select T.c.value('(SITEID)[1]','uniqueidentifier') from @SITES.nodes('/SITES/ITEM') T(c))
            set @SITES = null;

        declare @SITESFILTER nvarchar(max) = ' ((@SITES is null) or exists(select 1 from dbo.[CONSTITUENTSITE] inner join dbo.[UFN_CONSTITUENT_GETSITES_FROMITEMLISTXML](@SITES) [SITES] on [SITES].[SITEID] = [CONSTITUENTSITE].[SITEID] where [CONSTITUENTSITE].[CONSTITUENTID] = [CONTACTTABLE].[CONSTITUENTID]))';

        declare @sql nvarchar(max) = '
            select 
                [C].[ID],
                case
                    when exists(select [ID] from (
                        select [CONTACTTABLE].[ID], [CONTACTTABLE].[CONSTITUENTID] from dbo.[EMAILADDRESS] as [CONTACTTABLE] where @CHECKEMAIL = 1' + char(13);

        if @SITES is not null
          set @SQL = @SQL + 'and' + @SITESFILTER + char(13);

        set @SQL = @SQL + 'union all
                        select [CONTACTTABLE].[ID], [CONTACTTABLE].[CONSTITUENTID] from dbo.[PHONE] as [CONTACTTABLE] where @CHECKPHONE = 1' + char(13);

        if @SITES is not null
          set @SQL = @SQL + 'and' + @SITESFILTER + char(13);

        set @SQL = @SQL + 'union all
                        select [CONTACTTABLE].[ID], [CONTACTTABLE].[CONSTITUENTID] from dbo.[ADDRESS] as [CONTACTTABLE] where @CHECKADDRESS = 1' + char(13);

        if @SITES is not null
          set @SQL = @SQL + 'and' + @SITESFILTER + char(13);

        set @SQL = @SQL + ') as [A]
                where [A].[CONSTITUENTID] = [C].[ID])
                    then 1
                    else 0
                end as [VALUE]
            from dbo.[CONSTITUENT] as [C]' + char(13);

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

        -- Backwards compatibility

        if @SMARTFIELDID is null
        begin
          if @SITES is null
            exec sp_executesql @sql,
                N'@CHECKEMAIL bit,
                @CHECKPHONE bit,
                @CHECKADDRESS bit',
                @CHECKEMAIL,
                @CHECKPHONE,
                @CHECKADDRESS;
          else
            exec sp_executesql @sql,
                N'@CHECKEMAIL bit,
                @CHECKPHONE bit,
                @CHECKADDRESS bit,
                @SITES xml',
                @CHECKEMAIL,
                @CHECKPHONE,
                @CHECKADDRESS,
                @SITES;
        end
        else
        begin
            declare @TARGET nvarchar(100);
            declare @TARGET_FIELD nvarchar(128);
            select
                @TARGET = [TABLECATALOG].[TABLENAME],
                @TARGET_FIELD = [SMARTFIELD].[VALUECOLUMNNAME]
            from dbo.[SMARTFIELD]
                left join dbo.[TABLECATALOG] on [SMARTFIELD].[TABLECATALOGID] = [TABLECATALOG].[ID]
            where [SMARTFIELD].[ID] = @SMARTFIELDID;

            set @sql = '
                merge dbo.' + @TARGET + ' as target
                using (' + @sql + ') as source
                on target.ID = source.ID
                when matched then
                    update set ' + @TARGET_FIELD + ' = source.VALUE
                when not matched by target then
                    insert (ID, ' + @TARGET_FIELD + ')
                    values (source.ID, source.VALUE)
                when not matched by source then
                    delete;';

            if @SITES is null
          exec sp_executesql @sql,
                  N'@CHECKEMAIL bit,
                  @CHECKPHONE bit,
                  @CHECKADDRESS bit',
                  @CHECKEMAIL,
                  @CHECKPHONE,
                  @CHECKADDRESS;
            else
              exec sp_executesql @sql,
                  N'@CHECKEMAIL bit,
                  @CHECKPHONE bit,
                  @CHECKADDRESS bit,
                  @SITES xml',
                  @CHECKEMAIL,
                  @CHECKPHONE,
                  @CHECKADDRESS,
                  @SITES;

             select @@ROWCOUNT;
        end
    end