USP_DATAFORMTEMPLATE_EDIT_RELATIONSHIPSBYMARRIAGE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RELATIONSHIPSBYMARRIAGE(
  @ID nvarchar(72),
  @CHANGEAGENTID uniqueidentifier = null,
  @RELATIONSHIPS xml
)
as

  set nocount on;

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  declare @PARENTID uniqueidentifier;
  declare @CHILDID uniqueidentifier;

  set @PARENTID = convert(uniqueidentifier, substring(@ID, 0, 37));
  set @CHILDID = convert(uniqueidentifier, substring(@ID, 37, 37));

  begin try
    declare @TempTbl table 
    (
      ID uniqueidentifier,
      RECIPROCALTYPECODEID uniqueidentifier,
      RELATIONSHIPTYPECODEID uniqueidentifier
    )

    insert into @TempTbl 
      SELECT
        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(RECIPROCAL_TYPECODEID)[1]','uniqueidentifier') AS 'RECIPROCALTYPECODEID',
        T.c.value('(RELATIONSHIP_TYPECODEID)[1]','uniqueidentifier') AS 'RELATIONSHIPTYPECODEID'
      FROM @RELATIONSHIPS.nodes('/RELATIONSHIPS/ITEM') T(c)

    --delete removed items

    declare @RELATIONSHIPID uniqueidentifier;
    declare RELATIONSHIPCURSOR cursor local fast_forward for
      select 
        ID 
      from dbo.UFN_RELATIONSHIPSBYMARRIAGE(@PARENTID, @CHILDID) RELATIONSHIPS 
      except 
        select ID from @TempTbl;

    open RELATIONSHIPCURSOR;   

    fetch next from RELATIONSHIPCURSOR into @RELATIONSHIPID;

    while (@@FETCH_STATUS = 0)
    begin                    
      exec dbo.USP_RELATIONSHIP_DELETE @RELATIONSHIPID, @CHANGEAGENTID;            

      fetch next from RELATIONSHIPCURSOR into @RELATIONSHIPID;
    end

    --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

    close RELATIONSHIPCURSOR;
    deallocate RELATIONSHIPCURSOR;
    --update the remaining

    update dbo.RELATIONSHIP set
      RECIPROCALTYPECODEID = RELATIONSHIP_XML.RECIPROCALTYPECODEID,
      RELATIONSHIPTYPECODEID = RELATIONSHIP_XML.RELATIONSHIPTYPECODEID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    from dbo.RELATIONSHIP
    inner join @TempTbl RELATIONSHIP_XML
      on RELATIONSHIP_XML.ID = RELATIONSHIP.ID

  end try

  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

return 0;