USP_CONSTITUENTSMARTFIELD_CONSTITUENTEXISTSINSELECTION

      CREATE procedure dbo.USP_CONSTITUENTSMARTFIELD_CONSTITUENTEXISTSINSELECTION
      (
        @SELECTIONID uniqueidentifier,
        @ASOF datetime,
        @SITES xml = null
      )
      as
        /*
        This smart field will returns a boolean value indicating if constituents exists in a specific selection
        */

        set nocount on;

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

        /*
        --Exist in a Selection...
        */
        declare @SQL nvarchar(max) ='
        select 
            [CONSTITUENT].[ID],
            case                             
            when 
             [CONSTITUENT].[ID] in (select [ID] from dbo.[UFN_IDSETREADER_GETRESULTS_GUID](@SELECTIONID))
            then
              cast(1 as bit)
            else
              cast(0 as bit)
            end
          from dbo.[CONSTITUENT]' + char(13);

        if @SITES is not null
        begin
          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] = [CONSTITUENT].[ID]))';

          exec sp_executesql @sql, N'@SELECTIONID uniqueidentifier, @SITES xml', @SELECTIONID, @SITES;
        end
        else
          exec sp_executesql @sql, N'@SELECTIONID uniqueidentifier', @SELECTIONID;