UFN_MARRIAGERELATIONSHIPASSOCIATION_ISUNIQUE

CREATE function dbo.UFN_MARRIAGERELATIONSHIPASSOCIATION_ISUNIQUE
(
  @MARRIAGERELATIONSHIPID uniqueidentifier,
  @RELATIONSHIPTYPECODEID uniqueidentifier
)
returns bit
with execute as caller
as begin
  declare @DUPLICATECOUNT int = 0;

  declare @SPOUSEGENDERCODE tinyint;
  select @SPOUSEGENDERCODE = SPOUSEGENDERCODE from dbo.MARRIAGERELATIONSHIP where ID = @MARRIAGERELATIONSHIPID;

  select
    @DUPLICATECOUNT = count(*)
  from
    dbo.MARRIAGERELATIONSHIP
  inner join dbo.MARRIAGERELATIONSHIPASSOCIATION
    on MARRIAGERELATIONSHIPASSOCIATION.MARRIAGERELATIONSHIPID = MARRIAGERELATIONSHIP.ID
  where
    MARRIAGERELATIONSHIPASSOCIATION.RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID and
    (MARRIAGERELATIONSHIP.SPOUSEGENDERCODE = @SPOUSEGENDERCODE or @SPOUSEGENDERCODE = 3 or MARRIAGERELATIONSHIP.SPOUSEGENDERCODE = 3) and
    MARRIAGERELATIONSHIP.ID <> @MARRIAGERELATIONSHIPID;

  if (@DUPLICATECOUNT = 0)
    return 1;

  return 0;
end