USP_RELATIONSHIPBYMARRIAGE_CREATE

Inserts a new Relationship by Marriage record for a given relationship.

Parameters

Parameter	Parameter Type	Mode
@MARRIAGERELATIONSHIPID	uniqueidentifier	IN
@RELATIONSHIPID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN

Definition

Copy



create procedure dbo.USP_RELATIONSHIPBYMARRIAGE_CREATE
(
  @MARRIAGERELATIONSHIPID uniqueidentifier,
  @RELATIONSHIPID uniqueidentifier, 
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime
)
as
begin
  set nocount on;

  insert into dbo.RELATIONSHIPBYMARRIAGE
  (ID, MARRIAGERELATIONSHIPID, RELATIONSHIPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values
  (
    newid(),
    @MARRIAGERELATIONSHIPID,
    @RELATIONSHIPID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
  ) 

  insert into dbo.RELATIONSHIPBYMARRIAGE --reciprocal

  (ID, MARRIAGERELATIONSHIPID, RELATIONSHIPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select 
    newid(),
    @MARRIAGERELATIONSHIPID,
    A.ID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
  from dbo.RELATIONSHIP A
  inner join dbo.RELATIONSHIP B
    on A.RECIPROCALCONSTITUENTID = B.RELATIONSHIPCONSTITUENTID and 
      A.RELATIONSHIPCONSTITUENTID = B.RECIPROCALCONSTITUENTID and 
      A.RELATIONSHIPTYPECODEID = B.RECIPROCALTYPECODEID and
      A.RECIPROCALTYPECODEID = B.RELATIONSHIPTYPECODEID
  where B.ID = @RELATIONSHIPID;

end