USP_RELATIONSHIPS_ADDFROMXML

CREATE procedure dbo.USP_RELATIONSHIPS_ADDFROMXML
(
    @RELATIONSHIPS xml,
    @DEFAULTRELATIONSHIPID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
    set nocount on;

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

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate();

    declare @TempTbl table
    (
        ID uniqueidentifier,
        RELATIONSHIPCONSTITUENTID uniqueidentifier,
        RELATIONSHIPTYPECODEID uniqueidentifier,
        RECIPROCALCONSTITUENTID uniqueidentifier,
        RECIPROCALTYPECODEID uniqueidentifier,
        STARTDATE datetime,
        ISSPOUSE bit
    )

    insert into @TempTbl
    select
        coalesce(RELATIONSHIPS.ID, newid()),
        RELATIONSHIPS.RELATIONSHIPCONSTITUENTID,
        RELATIONSHIPS.RELATIONSHIPTYPECODEID,
        coalesce(RELATIONSHIPS.RECIPROCALCONSTITUENTID, DEFAULTRELATIONSHIP.RECIPROCALCONSTITUENTID),
        RELATIONSHIPS.RECIPROCALTYPECODEID,
        coalesce(RELATIONSHIPS.STARTDATE, DEFAULTRELATIONSHIP.STARTDATE),
        coalesce(RELATIONSHIPS.ISSPOUSE, 0)
    from
        dbo.UFN_RELATIONSHIPS_FROMITEMLISTXML(@RELATIONSHIPS) as RELATIONSHIPS
    left join
        dbo.RELATIONSHIP as DEFAULTRELATIONSHIP on DEFAULTRELATIONSHIP.ID = @DEFAULTRELATIONSHIPID

    declare @SETID uniqueidentifier = newid();

    insert into dbo.RELATIONSHIPSET
    (
      ID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @SETID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CHANGEDATE,
      @CHANGEDATE
    );

    insert into dbo.RELATIONSHIP
    (
        ID,
        RELATIONSHIPCONSTITUENTID,
        RELATIONSHIPTYPECODEID,
        RECIPROCALCONSTITUENTID,
        RECIPROCALTYPECODEID,
        STARTDATE,
        ISSPOUSE,
        RELATIONSHIPSETID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        [temp].ID,
        [temp].RELATIONSHIPCONSTITUENTID,
        [temp].RELATIONSHIPTYPECODEID,
        [temp].RECIPROCALCONSTITUENTID,
        [temp].RECIPROCALTYPECODEID,
        [temp].STARTDATE,
        [temp].ISSPOUSE,
        @SETID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CHANGEDATE,
        @CHANGEDATE
    from 
        @TempTbl as [temp]
    where 
        [temp].RELATIONSHIPTYPECODEID is not null 
        and [temp].RECIPROCALTYPECODEID is not null
        and not exists(select ID from dbo.RELATIONSHIP EXISTING where EXISTING.RELATIONSHIPCONSTITUENTID = [temp].RELATIONSHIPCONSTITUENTID 
            and EXISTING.RECIPROCALCONSTITUENTID = [temp].RECIPROCALCONSTITUENTID)

    if @@Error <> 0
        return 2;

    return 0;