USP_DATALIST_CONSTITUENTSELECTIONSTOTAL

CREATE procedure dbo.USP_DATALIST_CONSTITUENTSELECTIONSTOTAL
(
    @IDSETIDS nvarchar(max)
)
as
    set nocount on;

       declare @idtable table (id uniqueidentifier);
    declare @SQL nvarchar(max) = '';

    declare @idsettable table (idsetid uniqueidentifier);
    declare @delimiter as nvarchar(max) = ',';

    if len(@IDSETIDS) > 0
    begin
        -- Parse each set ID into a table var.

        declare @xml xml
        set @xml = N'<root><r>' + replace(@IDSETIDS,@delimiter,'</r><r>') + '</r></root>'
        insert into 
            @idsettable(idsetid)
        select
            r.value('.','uniqueidentifier') as idsetid
        from
             @xml.nodes('//root/r') as records(r)    

        -- Aggregate set id selection statements, concatenated with 'union'

        select 
            @SQL = @SQL + dbo.ufn_buildselectsql(idsetid) + ' union'
        from 
            @idsettable

        -- Strip trailing 'union' string.

        if LEN(@SQL) > 0
            set @SQL = substring(@SQL, 0, len(@SQL) - 5);

        -- Insert the Ids obtained from dynamic sql into a table var.

        insert into @idtable
        exec (@SQl);
    end

    declare @CONSTITUENTSTOTAL as decimal = 0;
    declare @CONSTITUENTSINCLUDED as decimal = 0;

    select
        @CONSTITUENTSTOTAL = COUNT(CONSTITUENT.ID)
    from
        CONSTITUENT
    left join    
        DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
    left join
        GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
    where
        CONSTITUENT.ISINACTIVE = 0
        and
        DECEASEDCONSTITUENT.ID is null
        and
        (    GROUPDATA.ID is null
            or
            GROUPDATA.GROUPTYPECODE <> 0
        )

    select
        @CONSTITUENTSINCLUDED = COUNT(CONSTITUENT.ID)
    from
        CONSTITUENT
    left join    
        DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
    left join
        GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
    where
        CONSTITUENT.ID in (select ID from @idtable)
        and
        CONSTITUENT.ISINACTIVE = 0
        and
        DECEASEDCONSTITUENT.ID is null
        and
        (   GROUPDATA.ID is null
            or
            GROUPDATA.GROUPTYPECODE <> 0
        )

    select @CONSTITUENTSINCLUDED, @CONSTITUENTSTOTAL;