USP_DATAFORMTEMPLATE_EDIT_GROUPMEMBER_3

create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GROUPMEMBER_3
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @STARTDATE date,
  @ENDDATE date,
  @COMMENTS nvarchar(300),
  @GROUPMEMBERDATERANGEID uniqueidentifier
)
as
  set nocount on;

  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE datetime = getdate();

  if @STARTDATE > @CURRENTDATE
  begin
    raiserror ('ERR_STARTDATE_CANNOT_BE_IN_FUTURE', 13, 1);
    return 1;
  end

  if @ENDDATE > @CURRENTDATE
  begin
    raiserror ('BBERR_ENDDATE_CANNOT_BE_IN_FUTURE', 13, 1);
    return 1;
  end

  declare @GROUPSTARTDATE datetime;

  select
    @GROUPSTARTDATE = GROUPDATA.STARTDATE
  from
    dbo.GROUPMEMBER
  inner join
    dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
  where
    GROUPMEMBER.ID = @ID

  if @STARTDATE is not null and @GROUPSTARTDATE is not null
    if @STARTDATE < @GROUPSTARTDATE
    begin
      raiserror ('ERR_STARTDATE_CANNOT_BE_BEFORE_GROUPSTARTDATE', 13, 1);
      return 1;
    end

  begin try
    if @ENDDATE is not null
      update dbo.GROUPMEMBER set
        ISPRIMARY = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @ID;

    update dbo.GROUPMEMBERDATERANGE set
      DATEFROM = @STARTDATE,
      DATETO = @ENDDATE,
      COMMENTS = @COMMENTS,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where
      --Multiple records with non-overlapping date ranges are valid, so we need to use the DateRangeID, not the GroupMemberID.
      --GROUPMEMBERID = @ID
      ID = @GROUPMEMBERDATERANGEID

  exec dbo.USP_GROUPMEMBERROLE_UPDATEDATES @ID, @CHANGEAGENTID;
  end try
  begin catch
  exec dbo.USP_RAISE_ERROR
  return 1
  end catch

  return 0;