USP_MULTIPLEGROUPMEMBERROLES_VALIDDATES

create procedure dbo.USP_MULTIPLEGROUPMEMBERROLES_VALIDDATES(
    @GROUPMEMBERID uniqueidentifier,
    @GROUPROLES xml
)
as

    declare @ROLES table (
       [ENDDATE] date,
       [GROUPMEMBERROLECODEID] uniqueidentifier,
       [ID] uniqueidentifier,
       [STARTDATE] date)

    insert into @ROLES select 
        [ENDDATE],
        [GROUPMEMBERROLECODEID],
        [ID],
        [STARTDATE] 
    from dbo.UFN_CONSTITUENT_GETGROUPROLES_FROMITEMLISTXML(@GROUPROLES)

    select * from @ROLES

    set nocount on;

    declare @CURRENTDATE date;
    set @CURRENTDATE = getdate();

    declare @GROUPMEMBERSTARTDATE date;
    declare @GROUPMEMBERENDDATE date;

    select
        @GROUPMEMBERSTARTDATE = DATEFROM,
        @GROUPMEMBERENDDATE = DATETO
    from
        dbo.GROUPMEMBERDATERANGE
    where GROUPMEMBERID = @GROUPMEMBERID

    if exists(
        select 1
        from @ROLES
        where STARTDATE is not null 
            and STARTDATE > @CURRENTDATE
    )
    begin    
        raiserror('ERR_STARTDATE_CANNOT_BE_IN_FUTURE', 13, 1)
        return 1
    end
    if exists(
        select 1
        from @ROLES
        where STARTDATE is not null 
            and STARTDATE < @GROUPMEMBERSTARTDATE
    )
    begin    
        raiserror('ERR_STARTDATE_CANNOT_BE_BEFORE_MEMBERSTART', 13, 1)
        return 1
    end
    if exists(
        select 1
        from @ROLES
        where STARTDATE is not null 
            and STARTDATE > @GROUPMEMBERENDDATE
    )
    begin    
        raiserror('ERR_STARTDATE_CANNOT_BE_AFTER_MEMBEREND', 13, 1)
        return 1
    end

    if exists(
        select 1
        from @ROLES
        where ENDDATE is not null 
            and ENDDATE < @GROUPMEMBERSTARTDATE
    )
    begin    
        raiserror('ERR_ENDDATE_CANNOT_BE_BEFORE_MEMBERSTART', 13, 1)
        return 1
    end
    if exists(
        select 1
        from @ROLES
        where ENDDATE is not null 
            and ENDDATE > @GROUPMEMBERENDDATE
    )
    begin    
        raiserror('ERR_ENDDATE_CANNOT_BE_AFTER_MEMBEREND', 13, 1)
        return 1
    end

    return 0;